August 19, 2014 at 1:44 pm
Hi everyone
Hope some one can offer some advise on this one. Know what I want to do but don't unfortunately have the knowledge to do it....
I have a spreadsheet that contains details of permissions that need to be removed for security reasons.
Spreadsheet has server, database, user and permission that needs removing.
My thought was to export this list into a table. Then create some SQL to read the table and then remove the permission listed.
If SQL statements could be generated for each removal task in the spreadsheet that would be ideal. I could then execute the TSQL where needed and use the generated scripts as evidence of removals conducted.
That's my ideal scenario but don't have required knowledge to do it. If anyone could provide a solution that I could try to understand and learn from that would be most appreciated .
Many thanks for any advise offered.
August 19, 2014 at 2:39 pm
How about using another column on the spreadsheet to generate the (revoke?deny?) statements by concatenating some values?
Then you'll just need to copy that column and execute it.
It's just a thought.
August 19, 2014 at 2:40 pm
You should be able to get the spreadsheet into a table by right-clicking on the database to load it into, selecting "Tasks", then "Import Data...", and go thru the wizard to load the table.
Once the table's loaded, we can help you write code to generate dynamic SQL and run it.
We'd need to know specifically what's in the "permissions" column, to know how to translate it into the equivalent SQL statement/command.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 19, 2014 at 2:53 pm
ScottPletcher (8/19/2014)
You should be able to get the spreadsheet into a table by right-clicking on the database to load it into, selecting "Tasks", then "Import Data...", and go thru the wizard to load the table.Once the table's loaded, we can help you write code to generate dynamic SQL and run it.
We'd need to know specifically what's in the "permissions" column, to know how to translate it into the equivalent SQL statement/command.
Scott
Many thanks for your reply.
I will get the table loaded asap and then let you know the column information.
For my own piece of mind , was what I was thinking about the best way to do it , is it easily achievable ?
thanks
August 19, 2014 at 2:57 pm
MickyD (8/19/2014)
ScottPletcher (8/19/2014)
You should be able to get the spreadsheet into a table by right-clicking on the database to load it into, selecting "Tasks", then "Import Data...", and go thru the wizard to load the table.Once the table's loaded, we can help you write code to generate dynamic SQL and run it.
We'd need to know specifically what's in the "permissions" column, to know how to translate it into the equivalent SQL statement/command.
Scott
Many thanks for your reply.
I will get the table loaded asap and then let you know the column information.
For my own piece of mind , was what I was thinking about the best way to do it , is it easily achievable ?
thanks
It should be, assuming the "permissions" are clear and accurate enough to translate into actual SQL permissions.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 19, 2014 at 3:44 pm
Scott hi
As requested. This will be the config of the table containing the removal data.
ServerNameDatabase UsernameRole Permissions ActionLON1\INSTAAgencies ABC\320xxxdb_owner,db_datareader,db_datawriterRemove
LON1\INSTAAgencies ABC\437xxxdb_datareaderRemove
if the script could output results of the removal to the results pane I would use that as evidence of task complete.
I.e. something like
LON1\INSTA ABC\320xxx db_owner,db_datareader,db_datawriter permission removed from Agencies
I plan to run the scripts against each server in turn at the moment. But going forward I might like to run it against a CMS containing the server names. Not sure if that will affect script.
Not too concerned with out put as long as I can understand output I can config to suit.
many thanks
August 19, 2014 at 4:11 pm
Something like this should get you going pretty quickly. You can add the "documentation" part by adding a SELECT and/or PRINT to output a message about what is about to happen.
CREATE TABLE #permissions (
ServerName sysname NULL,
[Database] sysname NULL,
Username sysname NULL,
[Role Permissions] varchar(8000) NULL,
Action varchar(50) NULL
)
TRUNCATE TABLE #permissions
INSERT INTO #permissions
SELECT 'LON1\INSTA', 'Agencies', 'ABC\320xxx', 'db_owner,db_datareader,db_datawriter', 'Remove' UNION ALL
SELECT 'LON1\INSTA', 'Agencies', 'ABC\437xxx', 'db_datareader', 'Remove'
DECLARE cursor_permissions CURSOR LOCAL FAST_FORWARD FOR
SELECT p.ServerName, p.[Database], p.Username, split.Item, p.Action
FROM #permissions p
CROSS APPLY dbo.DelimitedSplit8k( p.[Role Permissions], ',') AS split
--WHERE p.ServerName = @@SERVERNAME
ORDER BY [Database], Username, split.ItemNumber
DECLARE @ServerName sysname
DECLARE @Database sysname
DECLARE @Username sysname
DECLARE @Item varchar(100)
DECLARE @Action varchar(30)
DECLARE @sql nvarchar(4000)
OPEN cursor_permissions
WHILE 1 = 1
BEGIN
FETCH NEXT FROM cursor_permissions INTO @ServerName, @Database, @Username, @Item, @Action
IF @@FETCH_STATUS = -1
BREAK;
IF @@FETCH_STATUS = -2
CONTINUE;
IF LEFT(@Database, 1) = '['
SET @Database = REPLACE(REPLACE(@Database, '[', ''), ']', '')
IF LEFT(@Username, 1) <> '['
SET @Username = REPLACE(REPLACE(@Username, '[', ''), ']', '')
IF @Action = 'Remove'
BEGIN
SET @sql = 'USE [@Database]; EXEC sp_droprolemember ''@Item'', ''@Username'''
SET @sql = REPLACE(REPLACE(REPLACE(@sql,
'@Database', @Database),
'@Item', @Item),
'@Username', @Username)
PRINT @sql
--EXEC(@sql)
END --IF
END --WHILE
DEALLOCATE cursor_permissions
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 19, 2014 at 4:23 pm
Scott
Thanks for this . Could you confirm if I need to add the select statement part ?
Ideal situation would be to read this from a table that exists in another database ?
August 19, 2014 at 4:30 pm
MickyD (8/19/2014)
ScottThanks for this . Could you confirm if I need to add the select statement part ?
Ideal situation would be to read this from a table that exists in another database ?
Correct, you need to change the SELECT in the cursor to match your existing db. I used a temp table just to have data to test the code:
...
DECLARE ...
SELECT p.ServerName, p.[Database], p.Username, split.Item, p.Action
FROM /*linked_server_name.*/db_name.dbo.table_name p
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 19, 2014 at 4:38 pm
Thank you Scott
Will test and text again to ensure I understand the process.
Appreciate your efforts to help
August 20, 2014 at 1:35 am
Been working on this and still a bit stuck.
What I need to achieve is an output which will go thru the table of contents and dynamically produce an output script that I can then run.
Something like this.
EXEC master..sp_dropsrvrolemember @loginame = N'Bob', @rolename = N'sysadmin'
EXEC master..sp_dropsrvrolemember @loginame = N'Tom', @rolename = N'sysadmin'
EXEC master..sp_dropsrvrolemember @loginame = N'Joe', @rolename = N'dbcreator'
I have all the information in a SQL table , so just trying to pull that out into statement such as the above.
thanks
August 20, 2014 at 4:34 am
MickyD (8/20/2014)
Been working on this and still a bit stuck.What I need to achieve is an output which will go thru the table of contents and dynamically produce an output script that I can then run.
Something like this.
EXEC master..sp_dropsrvrolemember @loginame = N'Bob', @rolename = N'sysadmin'
EXEC master..sp_dropsrvrolemember @loginame = N'Tom', @rolename = N'sysadmin'
EXEC master..sp_dropsrvrolemember @loginame = N'Joe', @rolename = N'dbcreator'
I have all the information in a SQL table , so just trying to pull that out into statement such as the above.
thanks
Are you modifying just role memberships or does the permission list also deny or revoke object level permissions (i.e. removing select permission on table dbo.bob to user joe)??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 20, 2014 at 4:38 am
Only requirement is to modify the role memberships , no object level permission changes as this stage.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply