January 14, 2008 at 11:15 am
Hi,
i wrote this script that i need to deny access to some users from write to some database.
so i wrote this script that take all the users except the 'sa' and deny access from database com.
what i need i this script is how i insert all my databases as well in the 'use database' LOOP, so it will execute to all users on all databases?
Thx
declare @username as varchar(100)
declare @runcommand as varchar(100)
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD
FOR
SELECT [Name]
FROM master.dbo.syslogins WHERE NAME NOT IN ('sa')
OPEN CRS_db
FETCH NEXT FROM CRS_db INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
Set @runcommand='USE com; EXEC sp_addrolemember N''db_denydatawriter'', N''' + @username + ''''
exec (@runcommand)
FETCH NEXT FROM CRS_db INTO @username
END
CLOSE CRS_db
DEALLOCATE CRS_db
January 14, 2008 at 12:46 pm
If I am understanding you, you want to run this script for each database on your server.
You need to use the undocumented stored procedure from Microsoft called 'sp_MSforeachdb'
What you want to do is also use Dynamic SQL
Something like this:
SET @script = NVAR (8000)
@script = 'Your script goes here'
EXEC sp_MSfoeachdb @script
January 14, 2008 at 12:56 pm
This is a built-in function to apply an action to every database. I have modified it to exclude system databaeses.
declare @username as varchar(100)
declare @runcommand as varchar(100)
SET @username = 'TEST_USER'
EXEC master..sp_MSForeachdb '
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
EXEC sp_addrolemember N''db_denydatawriter'', N''' + @username + '''
END
'
January 14, 2008 at 1:03 pm
I used the same function that Jsheldon recommended. You will be able to adjust either one to suit you needs.
January 14, 2008 at 1:06 pm
can any one help me with my script or how i can insert the exec code into the sp_MSforeachdb?
Thx
January 14, 2008 at 1:11 pm
this script isn't working.
January 14, 2008 at 1:13 pm
What is your error?
January 14, 2008 at 1:15 pm
when i copy the script as is, i get
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '+'.
January 14, 2008 at 1:29 pm
declare @username as varchar(100)
declare @runcommand as varchar(100)
SET @username = 'test'
EXEC master..sp_MSForeachdb '
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
EXEC sp_addrolemember N''db_datareader'', N''@username''
END
'
January 14, 2008 at 2:01 pm
it's working,but the username variable isn't passing.why?
January 14, 2008 at 3:00 pm
Its because the set is outside the execution of the loop move the set = username in the loop.
EXEC master..sp_MSForeachdb '
USE [?]
DECLARE @username AS VARCHAR(25)
SET @username = ''test''
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
EXEC sp_addrolemember N''db_datareader'', @username
END
'
January 14, 2008 at 3:12 pm
This final bit of code should work for you. Note that you will want to make sure the user is in the database first. Your final code will look like this.
EXEC master..sp_MSForeachdb '
USE [?]
DECLARE @username AS VARCHAR(25)
SET @username = ''test''
IF EXISTS(SELECT UID FROM SYSUSERS WHERE name = @username)
BEGIN
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
EXEC sp_addrolemember N''db_denydatawriter'', @username
END
END
'
January 14, 2008 at 3:13 pm
I finally got around to testing it with a test db. I have tested the above code and it is wokring as intended.
January 14, 2008 at 3:30 pm
ok it's working.
now for the final phase,how in this script i can pass the variable for the all users in the "SELECT loginname FROM master.dbo.syslogins"
THX
January 14, 2008 at 4:25 pm
Ok here is the script. I would highely recommend that you put a filter on the where clause, so you do not grab system accounts or built-in accounts. You can change my where clause to suit your own needs. I have placed my where clause to only grab a login name 'test'
EXEC master..sp_MSForeachdb '
USE [?]
DECLARE @username AS VARCHAR(25), @Counter INT, @NbrLogins INT
SET @Counter = 1
SET @NbrLogins = (SELECT COUNT(*) FROM master.dbo.syslogins WHERE loginname = ''test'')
SELECT ROW_NUMBER() OVER (ORDER BY loginname) AS [ID], loginname
INTO #tmp_logins
FROM master.dbo.syslogins
WHERE loginname = ''test''
ORDER BY loginname
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
WHILE @Counter <= @NbrLogins
BEGIN
SET @username = (SELECT loginname FROM #tmp_logins WHERE @Counter = ID)
IF EXISTS(SELECT UID FROM SYSUSERS WHERE name = @username)
BEGIN
EXEC sp_addrolemember N''db_denydatawriter'', @username
END
SET @Counter = @Counter + 1
END
END
'
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply