SQLTeam.com | Weblogs | Forums

Add another column with some commands

i have a script below to find all the db fixed role db_datawriter. this is working fine, no issues. what i want to do is come up with a series of commands:

  • drop the member from the db
  • grant update, delete, insert to the member

So the 5th column will look something like this:

USE dbname ALTER ROLE [db_datawriter] DROP MEMBER UserName

This is what i have as the last select statement.

SELECT *, 'USE ' + @dbname + ' ALTER ROLE [db_datawriter] ' + 'DROP MEMBER ' + UserName FROM #UserPermission

However, the dbname doesn't change. it keeps listing out the first one. the rest of the string concat works fine.

DECLARE @dbname VARCHAR(50)  
DECLARE @statement NVARCHAR(MAX)

CREATE TABLE #UserPermission

(

 DbName SYSNAME,
UserName SYSNAME,
TypeOfLogIn VARCHAR(50),
PermissionLevel VARCHAR(50),
)

 

DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD

FOR 

SELECT name

FROM master.sys.databases

WHERE state_desc='online'

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @dbname 

WHILE @@FETCH_STATUS = 0 

BEGIN 

 

SELECT @statement = 'use '+@dbname +';'+ 'SELECT

 

dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

where pp.name=''db_datawriter'' and p.name<>''dbo'''

 

INSERT INTO #UserPermission

EXEC sp_executesql @statement

 

FETCH NEXT FROM db_cursor INTO @dbname 

END 

CLOSE db_cursor 

DEALLOCATE db_cursor

SELECT * FROM #UserPermission

DROP TABLE #UserPermission

db_datawriter gives INSERT, UPDATE, and DELETE permissions. Why are you doing the above?

good question.

basically the database security audit team ran guardium scan and found a finding that say fixed db roles such as datawriter, datareader, etc must not be assigned directly to user accounts. and thus, this is what they recommended. remove the fixed roles and grant them the same permission as the fixed role.

I would ask them why exactly. Lotaa people suggest a lotta things just to say they did something and they might be absolutely right but I would be curious as to why?

Why not create a new role/ad group, give that entity the perms and add the users to that role/group

Someone leaves the comoany, no problem, it is a group perm

Not sure what you mean by member

i agree with your comments. no arguments from me.

however, back to my original question, how would i do this?

I agree with the security team. Instead of assigning permissions to a use, assign to an AD Group and have the users put in the group. It's easier to maintain and add new users. As far as your query, I just ran this and it works fine, but I had access to all the databases as I was sysadmin on SQL. I ran it on another and received an error as I didn't have access to some of the dbs. I would change this to add square brackets, but otherwise it looks ok

'use ['+@dbname +'];'

same thing mike01. i added the brackets as suggested, it still showing up the same DBs for all

hi

i feel like what you are doing is NOT required

please see the script i am putting
and see the print statement

make your results RESULTS to Text Option

once you are confident the script is OK

Open a new window .. copy paste the script and run it

DECLARE @ExecString nvarchar(max)

SELECT  
     @ExecString =  ' USE '+x.name + ' ; GO ;'+ ' ALTER ROLE [db_datawriter] ' + 'DROP MEMBER ' + p.Name
FROM 
   sys.databases x,
   sys.database_role_members roles JOIN sys.database_principals p  ON roles.member_principal_id = p.principal_id
                                   JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
WHERE 
   pp.name='db_datawriter' and p.name<>'dbo'

SELECT @ExecString = Replace(@ExecString,';',char(10))
PRINT  @ExecString

@harishgg1

no results.

only has completion time. that's it

@harishgg1 same regardless if i sent to txt or send to screen.

just a completion time. no results.

to clarify, your script replace mine, correct?

check your script once

the query does not have data that matches

or

you may not have permissions to view the data

check your script once

the query does not have data that matches

or

you may not have permissions to view the data