Create user with the following permissions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Create user with the following permissions

Create user with the following permissions

Rate Topic

Display Mode

Topic Options

Author

Message

Sanz

Posted Tuesday, March 31, 2009 7:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:11 PM
Points: 164, Visits: 702

Hi,

I need to create a user with the following permissions.

Create/Alter/Drop Procedure

The user should not be able to Create/Alter/Drop Tables.

Thanx in advance !

Sanz

Post #686970

MSQLDBA

Posted Tuesday, March 31, 2009 7:07 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 21, 2012 8:49 AM
Points: 129, Visits: 551

check this

grant user on sp

________________________________________
M.I.

Learning is a path with no destination...

Post #686977

Sanz

Posted Tuesday, March 31, 2009 7:44 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:11 PM
Points: 164, Visits: 702

The user should have Create/Alter/Drop permissions for all the Sp's in the database but should not have Create/Alter/Drop Table permissions in the database.

Sanz

Post #687039

Greg Charles

Posted Tuesday, March 31, 2009 10:16 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:08 PM
Points: 4,006, Visits: 4,845

I know you can grant CREATE PROCEDURE permission to a user or role, which would allow it to create stored procedures, but you have to grant ALTER permission individually to each procedure after it's created. I think you can grant ALTER permission to the schema to which procedures belong to allow dropping procedures.

Greg

Post #687212

Sanz

Posted Tuesday, March 31, 2009 10:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:11 PM
Points: 164, Visits: 702

Most of the objects in this database are under the default schema (dbo).
So giving permissions on the schema won't work.

Sanz

Post #687235

SA-1

Posted Tuesday, March 31, 2009 12:33 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 7:35 AM
Points: 1,140, Visits: 933

I believe you will need to provide

ALTER ANY SCHEMA
CREATE PROCEDURE
EXECUTE

privileges on the database.

Post #687321

sjaiswal00

Posted Friday, August 27, 2010 1:45 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:57 AM
Points: 222, Visits: 311

If we provide
grant alter on schema ::dbo TO user1
then user1 will also be able to drop the tables.
... So then you DENY CONTROL on OBJECT::Table1 to user1 ... User1 is now prevented from dropping Table1

... But User1 can't even select from Table1 anymore. Since DENY is higher precedence than GRANT, you are unable to even GRANT SELECT on object::Table1 to User1

Post #976727

« Prev Topic | Next Topic »

Permissions