Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create user with the following permissions Expand / Collapse
Author
Message
Posted Tuesday, March 31, 2009 7:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:16 AM
Points: 184, Visits: 863
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
Posted Tuesday, March 31, 2009 7:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:43 AM
Points: 131, Visits: 558
check this

grant user on sp



________________________________________
M.I.

Learning is a path with no destination...

Post #686977
Posted Tuesday, March 31, 2009 7:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:16 AM
Points: 184, Visits: 863
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
Posted Tuesday, March 31, 2009 10:16 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:13 AM
Points: 4,064, Visits: 5,360
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
Posted Tuesday, March 31, 2009 10:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:16 AM
Points: 184, Visits: 863
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
Posted Tuesday, March 31, 2009 12:33 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 2:58 PM
Points: 1,141, Visits: 944
I believe you will need to provide

ALTER ANY SCHEMA
CREATE PROCEDURE
EXECUTE

privileges on the database.
Post #687321
Posted Friday, August 27, 2010 1:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 18, 2014 12:41 PM
Points: 222, Visits: 348
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 »

Add to briefcase

Permissions Expand / Collapse