TechTalkz.com Logo Ask the Experts!

Go Back   TechTalkz.com Technology & Computer Troubleshooting Forums > Tech Support Archives > Microsoft > Microsoft SQL Server

The specified schema name "dbo" either does not exist

Microsoft SQL Server

 
 
Thread Tools Display Modes
Unread 07-12-2007, 11:53 PM   #1
guruprasathb@gmail.com
Guest
 
Posts: n/a
The specified schema name "dbo" either does not exist

Hi,

I am new to SQL Server 2005. I am facing problem while i am trying to
give permissions on a particular user.

My Objective is, an SQL User is wants to manipulate Data and Create/
Execute Procedure. Only These privileges should have.


Use Master
Create Login LoginName with password = ' '

Use Databasename
Create User userName For Login LoginName
Create Role RoleName

Exec sp_addrolemember 'db_datareader', RoleName
Exec sp_addrolemember 'db_datawriter', RoleName
Exec sp_addrolemember @RoleName = 'RoleName', @MemberName = 'Username'

Grant Execute TO UserName
Grant Create Procedure TO UserName


But, After this, i am login in the new user
I am trying to create Procedure...But i am getting this error.

"Msg 2760, Level 16, State 1, Procedure t1, Line 4
The specified schema name "dbo" either does not exist or you do not
have permission to use it."


Please push me to come out of this situvation. Thanks in Advance.

 
Unread 07-12-2007, 11:53 PM   #2
Erland Sommarskog
Guest
 
Posts: n/a
Re: The specified schema name "dbo" either does not exist

(guruprasathb***********) writes:
> Grant Execute TO UserName
> Grant Create Procedure TO UserName
>
>
> But, After this, i am login in the new user
> I am trying to create Procedure...But i am getting this error.
>
> "Msg 2760, Level 16, State 1, Procedure t1, Line 4
> The specified schema name "dbo" either does not exist or you do not
> have permission to use it."


You need this:

GRANT ALTER ON SCHEMA::dbo TO RoleName

I think ALTER is sufficient, but you need CONTROL. This should be clear
from the Permissions section in the CREATE PROCEDURE topic in Books Online.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
 
Unread 07-12-2007, 11:53 PM   #3
Erland Sommarskog
Guest
 
Posts: n/a
Re: The specified schema name "dbo" either does not exist

(guruprasathb***********) writes:
> Grant Execute TO UserName
> Grant Create Procedure TO UserName
>
>
> But, After this, i am login in the new user
> I am trying to create Procedure...But i am getting this error.
>
> "Msg 2760, Level 16, State 1, Procedure t1, Line 4
> The specified schema name "dbo" either does not exist or you do not
> have permission to use it."


You need this:

GRANT ALTER ON SCHEMA::dbo TO RoleName

I think ALTER is sufficient, but you need CONTROL. This should be clear
from the Permissions section in the CREATE PROCEDURE topic in Books Online.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
 
Unread 07-12-2007, 11:54 PM   #4
Erland Sommarskog
Guest
 
Posts: n/a
Re: The specified schema name "dbo" either does not exist

(guruprasathb***********) writes:
> Grant Execute TO UserName
> Grant Create Procedure TO UserName
>
>
> But, After this, i am login in the new user
> I am trying to create Procedure...But i am getting this error.
>
> "Msg 2760, Level 16, State 1, Procedure t1, Line 4
> The specified schema name "dbo" either does not exist or you do not
> have permission to use it."


You need this:

GRANT ALTER ON SCHEMA::dbo TO RoleName

I think ALTER is sufficient, but you need CONTROL. This should be clear
from the Permissions section in the CREATE PROCEDURE topic in Books Online.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
 
Unread 07-12-2007, 11:54 PM   #5
guruprasathb@gmail.com
Guest
 
Posts: n/a
Re: The specified schema name "dbo" either does not exist

On Dec 6, 7:12 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (guruprasa...***********) writes:
> > Grant Execute TO UserName
> > Grant Create Procedure TO UserName

>
> > But, After this, i am login in the new user
> > I am trying to create Procedure...But i am getting this error.

>
> > "Msg 2760, Level 16, State 1, Procedure t1, Line 4
> > The specified schema name "dbo" either does not exist or you do not
> > have permission to use it."

>
> You need this:
>
> GRANT ALTER ON SCHEMA::dbo TO RoleName
>
> I think ALTER is sufficient, but you need CONTROL. This should be clear
> from the Permissions section in the CREATE PROCEDURE topic in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


Hi Erland Sommarskog,

Thanks a lot. Its worked fine....

 
Unread 07-12-2007, 11:54 PM   #6
guruprasathb@gmail.com
Guest
 
Posts: n/a
Re: The specified schema name "dbo" either does not exist

On Dec 6, 7:12 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (guruprasa...***********) writes:
> > Grant Execute TO UserName
> > Grant Create Procedure TO UserName

>
> > But, After this, i am login in the new user
> > I am trying to create Procedure...But i am getting this error.

>
> > "Msg 2760, Level 16, State 1, Procedure t1, Line 4
> > The specified schema name "dbo" either does not exist or you do not
> > have permission to use it."

>
> You need this:
>
> GRANT ALTER ON SCHEMA::dbo TO RoleName
>
> I think ALTER is sufficient, but you need CONTROL. This should be clear
> from the Permissions section in the CREATE PROCEDURE topic in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


Hi Erland Sommarskog,

Thanks a lot. Its worked fine....

 
Unread 07-12-2007, 11:55 PM   #7
guruprasathb@gmail.com
Guest
 
Posts: n/a
Re: The specified schema name "dbo" either does not exist

On Dec 6, 7:12 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (guruprasa...***********) writes:
> > Grant Execute TO UserName
> > Grant Create Procedure TO UserName

>
> > But, After this, i am login in the new user
> > I am trying to create Procedure...But i am getting this error.

>
> > "Msg 2760, Level 16, State 1, Procedure t1, Line 4
> > The specified schema name "dbo" either does not exist or you do not
> > have permission to use it."

>
> You need this:
>
> GRANT ALTER ON SCHEMA::dbo TO RoleName
>
> I think ALTER is sufficient, but you need CONTROL. This should be clear
> from the Permissions section in the CREATE PROCEDURE topic in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


Hi Erland Sommarskog,

Thanks a lot. Its worked fine....

 
Unread 08-12-2007, 12:02 AM   #8
guruprasathb@gmail.com
Guest
 
Posts: n/a
Re: The specified schema name "dbo" either does not exist

On Dec 6, 7:12 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (guruprasa...***********) writes:
> > Grant Execute TO UserName
> > Grant Create Procedure TO UserName

>
> > But, After this, i am login in the new user
> > I am trying to create Procedure...But i am getting this error.

>
> > "Msg 2760, Level 16, State 1, Procedure t1, Line 4
> > The specified schema name "dbo" either does not exist or you do not
> > have permission to use it."

>
> You need this:
>
> GRANT ALTER ON SCHEMA::dbo TO RoleName
>
> I think ALTER is sufficient, but you need CONTROL. This should be clear
> from the Permissions section in the CREATE PROCEDURE topic in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


Hi,

GRANT ALTER ON SCHEMA::dbo TO RoleName....After i executed this Grant
Privileges..my objective is not filled.
I am able to Alter / DROP Table.

But, I need to control the User not able to Alter / Drop table.

My Requirements:
As SQL USER should have following privileges.
1. db_datareader
2. db_datawriter
3. Create / Alter / Procedure
4. Deny for Create / Alter / Drop table on this New SQL User.

Thanks in Advance..

 
Unread 08-12-2007, 12:04 AM   #9
guruprasathb@gmail.com
Guest
 
Posts: n/a
Re: The specified schema name "dbo" either does not exist

>>GRANT ALTER ON SCHEMA::dbo TO RoleName <<
Can I set permissions on object type. Giving a Alter to schema makes
tables Alter permission.

I want the ALTER permission on Procedures and not other objects types
(Tables,functions).

This should be applicable to the new objects created also.

Thanks
Guru.



 
Unread 08-12-2007, 12:06 AM   #10
guruprasathb@gmail.com
Guest
 
Posts: n/a
Re: The specified schema name "dbo" either does not exist

On Dec 6, 7:12 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (guruprasa...***********) writes:
> > Grant Execute TO UserName
> > Grant Create Procedure TO UserName

>
> > But, After this, i am login in the new user
> > I am trying to create Procedure...But i am getting this error.

>
> > "Msg 2760, Level 16, State 1, Procedure t1, Line 4
> > The specified schema name "dbo" either does not exist or you do not
> > have permission to use it."

>
> You need this:
>
> GRANT ALTER ON SCHEMA::dbo TO RoleName
>
> I think ALTER is sufficient, but you need CONTROL. This should be clear
> from the Permissions section in the CREATE PROCEDURE topic in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


Hi,

GRANT ALTER ON SCHEMA::dbo TO RoleName....After i executed this Grant
Privileges..my objective is not filled.
I am able to Alter / DROP Table.

But, I need to control the User not able to Alter / Drop table.

My Requirements:
As SQL USER should have following privileges.
1. db_datareader
2. db_datawriter
3. Create / Alter / Procedure
4. Deny for Create / Alter / Drop table on this New SQL User.

Thanks in Advance..

 
 

Thread Tools
Display Modes



< Home - Windows Help - MS Office Help - Hardware Support >


New To Site? Need Help?

All times are GMT. The time now is 03:38 AM.


vBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO
Copyright © 2005-2013, TechTalkz.com. All Rights Reserved - Privacy Policy
Valid XHTML 1.0 Transitional