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 -
I guess I don't understand why you need to do this. The role grants those privs. Why do you want/need to do it separately?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.
however aside from this, from my original question - how would i do this?
-
I assume this works correctly for you:
DECLARE @dbname VARCHAR(50);
DECLARE @statement NVARCHAR(MAX);
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 = N'use ' + @dbname + N';' + N'SELECT DB_NAME()';
SELECT @statement;
FETCH NEXT FROM db_cursor
INTO @dbname;
END;I'm guessing you mean that the USE statement doesn't work correctly when you are trying to alter permissions. Is that correct?
this seems to work and find the datawriters for me:
DECLARE @dbname VARCHAR(50);
DECLARE @statement NVARCHAR(MAX);
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 = N'use ' + @dbname + N';' +
N'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'';
';
EXEC sp_executesql @statement;
FETCH NEXT FROM db_cursor
INTO @dbname;
END;
GO -
@steve-2 - the script to find the datawriter works fine. however, i'd like to include another column to drop the members of this result.
this is what i have for the last SELECT statement:
SELECT *, 'USE ' + @dbname + ' ALTER ROLE [db_datawriter] ' + 'DROP MEMBER ' + UserName FROM #UserPermission
however, the database name (@dbname) is not changing to the current selected database. it keeps repeating the same dbname.
-
I'm not quite clear what you're trying to do with #userpermission and the execute. You get to execute one statement. If you want to do a couple things, then run them sequentially and insert the results into the table, like a log. Or you can
insert #userpermission (dropaction) exec sp_executesql @dropstatement
or
better,
exec @result = exec(@dropstatement)
exec @result2 = exec(@alterstatement)
insert #userpermission (dropstatement, alterstatement) values @result, @result2I think you need to print out each statement and see what you're trying to execute, not put this in the table. Once you have the statements correct, then work on the
-
The top part of this screenshot is what it showing up after i use that last select statement. Notice the DBnamd keeps saying ABC.
the bottom portion is what i want.
i'm very new to SQL and still learning. i copied the script from someone else and not sure i understand what it does. i'd like to modify it to fit my needs but having issues.
-
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.
Rather misguided. They want to drastically increase the complexity and volume of permissions to adhere to this arbitrary rule?
Even if avoiding assignment of the built-in roles to users is a valid goal, a better way would be to assign the built-in roles to your own database roles that align with the permissions for the users specific work role, and assign the users to those custom database roles.
-
Apologies, I didn't realize where your problem was. That's why showing results and desired results is helpful.
If you want the dbname in the table, you need to insert it. What you've done with your last SELECT statement is outside of the cursor. The cursor is a loop, which has a changing @dbname with each loop. Once you get past these lines:
END
CLOSE db_cursor
DEALLOCATE db_cursornothing is changing the variable now. In this case, you are returning different values for each row in the #userpermission table, but then using a static variable. What you don't want it the @dbname, but rather the column.
Here's something that might work better
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
-- Get all roles
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 p.name<>''dbo'''
INSERT INTO #UserPermission EXEC sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- db_datawriter
SELECT *, 'use ' + dbname + '; alter role db_datawriter drop member [' + UserName + ']' FROM #UserPermission
WHERE PermissionLevel = 'db_datawriter'
-- db_datareader
SELECT *, 'use ' + dbname + '; alter role db_datawriter drop member [' + UserName + ']' FROM #UserPermission
WHERE PermissionLevel = 'db_datareader'
DROP TABLE #UserPermission -
Also, using these built-in roles isn't a problem, if the users need to see data in all tables. If they should be limited to some tables, then create a database role and assign the appropriate permissions.
-
Steve's solution looks like it will work, but I had already started testing your code with sp_MSforeachdb. I had to change it to reader was we don't have any writers. This doesn't check whether the database it online and it uses a global temp table so the insert can be inside the dynamic sql.
DROP TABLE IF EXISTS ##UserPermission
DECLARE @statement NVARCHAR(MAX)
CREATE TABLE ##UserPermission
(
DbName SYSNAME,
UserName SYSNAME,
TypeOfLogIn VARCHAR(50),
PermissionLevel VARCHAR(50),
)
SELECT @statement = 'use ? insert ##UserPermission 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_datareader'' and p.name<>''dbo'''
EXEC sp_MSforeachdb @statement
SELECT * FROM ##UserPermission
DROP TABLE IF EXISTS ##UserPermission -
Thanks so much Steve. it worked.
thanks sandwich.
-
Cheers. Make sure you understand how this works. If you have questions, please feel free to ask.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply