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
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
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
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
