User Permissions Management -- Best Practices?

Question

User Permissions Management -- Best Practices?

Barkingdog

SSCoach

Points: 18670
More actions
September 8, 2008 at 8:22 pm

#127387

We created a new databse and the initial users have been set up. They need to execute stored procs and Insert/Update in all tables in the DB. Other users may be added later and new stored procs may aso be added.
What is the easiest way, for the DBA, short of making the users "dbo" equivalent, to give them the access they need? (Granting Insert\Update, EXEC to individual users gets old fast.)
TIA,
barkingdog

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I create a role called Sp_exec.

I grant sp_exec permissions to execute all the sps on the server (open a cursor on all the sps and grant exec that way).

Then when you add a user, add that user to the role and you're done... you just need to add denies as need be, or just make a separate role.

Barkingdog SSCoach Points: 18670 More actions · Answer 2

Thanks for the good answer.

Of course if a new stored proc is added to a database (I'm not the only DBA type around) then users in the existing role won't has exec permission on it. Do you run the "enumerate the sp's names" routine daily or is no one but a single DBA allowed to create stored procs (and has the complete responsibilty of updating the sp enumeration)?

Barkingdog

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Just change your create sp code to this :

CREATE PROCEDURE dbo.Whatever

AS

--...

GO

GRANT EXECUTE ON dbo.Whatever TO SP_EXEC

GO

Of course if you don't have a change management system in place, it might be safe to have a script you run everytime you update production systems to see if any permissions are missing.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

That's what I use to grant permissions on everything at once (only SPs included)

CREATE ROLE SP_EXEC AUTHORIZATION dbo

--User for RAC

CREATE USER TestIdeal FOR LOGIN TestIdeal

--User for Local system

CREATE USER Ideal_Intranet FOR LOGIN [NT AUTHORITY\NETWORK SERVICE]

GO

EXEC sp_addrolemember N'SP_EXEC', N'TestIdeal'

EXEC sp_addrolemember N'SP_EXEC', N'Ideal_INTranet'

GO

--SET Permissions to EXEC ALL SPs to ALL Users

DECLARE AllObjects CURSOR FOR

SELECT 'GRANT EXECUTE ON dbo.' + Name + ' TO SP_EXEC' FROM Sys.SysObjects WHERE Type = 'P'

OPEN AllObjects

DECLARE @COMMAND VARCHAR(500)

FETCH NEXT FROM AllObjects INTO @COMMAND

WHILE (@@FETCH_STATUS = 0)

BEGIN

--PRINT @Command

EXEC (@COMMAND)

FETCH NEXT FROM AllObjects INTO @COMMAND

END

CLOSE AllObjects

DEALLOCATE AllObjects

GO