duplicated explicit permissions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

duplicated explicit permissions

Author

Topic

DJigli
Starting Member

4 Posts

Posted - 2009-11-25 : 05:58:49

Hi all.

My question is about security permissions on sql server 2005 database

Program I am using handles users (adding, removing) and their permissions trough stored procedures. Meaning I have procedure for adding login to server and user to database, procedure for removing, procedure for asigning permissions, etc.

My problem is handling permission for users. When two users (one and two) assings different permissions (grant and deny for select for example) to other user (three), that user (three) has two lines of asigned explicit permission, and efective permission usualy is deny.

Is there a way to both permissions go under one explicit permission other than impersonating one single user?
If that is not possible, is there a easy way to revoke both permissions (user one and two) when asigning new one, other than impersonating owner?

Thenx

DJigli
Starting Member

4 Posts

Posted - 2009-12-07 : 05:04:27

Anybody...?

Am I in the right forum at least?

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-08 : 07:50:17

Can u pls post the code here how u r doing this in SP ?

DJigli
Starting Member

4 Posts

Posted - 2009-12-09 : 09:10:23

Here is it:
There are three input parameters:

@Action -from 0 to 7 defines what permissions to give to @User
@User -database user name
@Modul -name of schema

DECLARE @grantSQL nvarchar(MAX)
DECLARE @denySQL nvarchar(MAX)

IF (1 & @Action) = 1
BEGIN -- ALLOW READ

SET @grantSQL = 'SELECT, VIEW DEFINITION'

END
ELSE IF (1 & @Action) = 0
BEGIN -- DENY READ

SET @denySQL = 'SELECT, VIEW DEFINITION'

END

IF (2 & @Action) = 2
BEGIN -- ALLOW WRITE

IF NOT @grantSQL IS NULL
BEGIN
SET @grantSQL = @grantSQL + ', INSERT, UPDATE'
END
ELSE
BEGIN
SET @grantSQL = 'INSERT, UPDATE'
END

END
ELSE IF (2 & @Action) = 0
BEGIN -- DENY WRITE

IF NOT @denySQL IS NULL
BEGIN
SET @denySQL = @denySQL + ', INSERT, UPDATE'
END
ELSE
BEGIN
SET @denySQL = 'INSERT, UPDATE'
END

END

IF (4 & @Action) = 4
BEGIN -- ALLOW REMOVE

IF NOT @grantSQL IS NULL
BEGIN
SET @grantSQL = @grantSQL + ', DELETE'
END
ELSE
BEGIN
SET @grantSQL = 'DELETE'
END

END
ELSE IF (4 & @Action) = 0
BEGIN -- DENY REMOVE

IF NOT @denySQL IS NULL
BEGIN
SET @denySQL = @denySQL + ', DELETE'
END
ELSE
BEGIN
SET @denySQL = 'DELETE'
END

END

IF NOT (@grantSQL IS NULL)
BEGIN --IF ANY PERMISSION IS GRANTED, EXECUTE IS ALSO GRANTED

SET @grantSQL = 'EXECUTE, ' + @grantSQL

SET @grantSQL = 'GRANT ' + @grantSQL + ' ON SCHEMA::' + @Modul + ' TO ' + @User

EXEC (@grantSQL)

END

IF NOT @denySQL IS NULL
BEGIN

IF @grantSQL IS NULL
BEGIN

SET @denySQL = 'EXECUTE, ' + @denySQL

END

SET @denySQL = 'REVOKE ' + @denySQL + ' ON SCHEMA::' + @Modul + ' TO ' + @User + ' CASCADE'

EXEC (@denySQL)

END

DJigli
Starting Member

4 Posts

Posted - 2009-12-15 : 05:10:19

I found solution

I created a role with administrative permissions (Admin_Role) and used
'GRANT ' + @grantSQL + ' ON SCHEMA::' + @Modul + ' TO ' + @User + ' AS Admin_Role'

And now every permission is enlisted as granted by Admin_Role

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources