February 24, 2014 at 6:25 am
when i executed the script
CREATE TABLE #DBROLES
( DBName sysname not null,
UserName sysname not null,
db_owner varchar(3) not null,
db_accessadmin varchar(3) not null,
db_securityadmin varchar(3) not null,
db_ddladmin varchar(3) not null,
db_datareader varchar(3) not null,
db_datawriter varchar(3) not null,
db_denydatawriter varchar(3) not null,
db_denydatareader varchar(3) not null,
db_sysadmin varchar(3) not null,
db_serveradmin varchar(3) not null,
db_processadmin varchar(3) not null,
db_dbcreator varchar(3) not null,
Cur_Date datetime not null default getdate()
)
DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = 'Insert into #DBROLES ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,db_sysadmin,db_serveradmin,db_processadmin,db_dbcreator,
db_denydatareader, db_denydatawriter)
declare @dbname varchar(200)
declare @mSql1 varchar(5000)
SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_processadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_processadmin,
Max(CASE RoleName WHEN ''db_dbcreator'' THEN ''Yes'' ELSE ''No'' END) AS db_dbcreator,
Max(CASE RoleName WHEN ''db_serveradmin'' THEN ''Yes'' ELSE ''No'' END) AS db_serveradmin,
Max(CASE RoleName WHEN ''db_sysadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_sysadmin,
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
Execute (@mSql1)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go
--select * from #DBROLES
--Drop Table #DBROLES
--------------
EXEC master.sys.sp_helpsrvrolemember
giving error as
must declare a scalar varaible @dbname in the line
SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
how to slove this .
February 24, 2014 at 6:28 am
Should it not be
SELECT DB_NAME()
February 24, 2014 at 6:32 am
sorry can you pls modify the script
February 24, 2014 at 6:33 am
FETCH NEXT FROM DBName_Cursor INTO @dbname
.
.
.
.
declare @dbname varchar(200)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 24, 2014 at 6:37 am
My answer was wrong anyway :blush:. It turns out the variables you are using were not declared (but you had attempted to declare them but in the wrong place)
Amended script
CREATE TABLE #DBROLES
(
DBName SYSNAME NOT NULL ,
UserName SYSNAME NOT NULL ,
db_owner VARCHAR(3) NOT NULL ,
db_accessadmin VARCHAR(3) NOT NULL ,
db_securityadmin VARCHAR(3) NOT NULL ,
db_ddladmin VARCHAR(3) NOT NULL ,
db_datareader VARCHAR(3) NOT NULL ,
db_datawriter VARCHAR(3) NOT NULL ,
db_denydatawriter VARCHAR(3) NOT NULL ,
db_denydatareader VARCHAR(3) NOT NULL ,
db_sysadmin VARCHAR(3) NOT NULL ,
db_serveradmin VARCHAR(3) NOT NULL ,
db_processadmin VARCHAR(3) NOT NULL ,
db_dbcreator VARCHAR(3) NOT NULL ,
Cur_Date DATETIME NOT NULL
DEFAULT GETDATE()
)
DECLARE @dbname NVARCHAR(200)
DECLARE @mSQL1 NVARCHAR(MAX)
DECLARE DBName_Cursor CURSOR
FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ( 'mssecurity', 'tempdb' )
ORDER BY name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @mSQL1 = 'Insert into #DBROLES ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,db_sysadmin,db_serveradmin,db_processadmin,db_dbcreator,
db_denydatareader, db_denydatawriter)
SELECT ' + '''' + @dbname + '''' + ' as DBName ,UserName, ' + CHAR(13)
+ '
Max(CASE RoleName WHEN ''db_processadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_processadmin,
Max(CASE RoleName WHEN ''db_dbcreator'' THEN ''Yes'' ELSE ''No'' END) AS db_dbcreator,
Max(CASE RoleName WHEN ''db_serveradmin'' THEN ''Yes'' ELSE ''No'' END) AS db_serveradmin,
Max(CASE RoleName WHEN ''db_sysadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_sysadmin,
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbname + '.dbo.sysmembers a ' + CHAR(13) + ' join ' + @dbname
+ '.dbo.sysusers b ' + CHAR(13) + ' on a.memberuid = b.uid join '
+ DB_NAME() + '.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
-- Remove the comments below if you want to execute this!
--EXECUTE (@mSql1)
PRINT @msql1
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go
--select * from #DBROLES
--Drop Table #DBROLES
February 24, 2014 at 6:42 am
thanks for the replies can pls resend the script by modifing
February 24, 2014 at 6:49 am
I have. It's the script above your last post unless I'm totally missing something
February 24, 2014 at 6:54 am
ramyours2003 (2/24/2014)
thanks for the replies can pls resend the script by modifing
Posters are always encouraged to participate. Have you tried moving the variable declaration?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 24, 2014 at 7:00 am
thanks mich and chris , i tried by amending the variables but got the error
mich i executed the script which u paste but got errot in the
CREATE TABLE #DBROLES
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #1: Cannot find data type SYSNAME.
February 24, 2014 at 7:03 am
Did you copy the 2nd script I posted? just checked the output on mine and it seems ok
Edit - Just ran that script and the output it generated and all was well. (SQL 2008 R2)
February 24, 2014 at 7:05 am
@mich
CREATE TABLE #DBROLES
(
DBName SYSNAME NOT NULL ,
UserName SYSNAME NOT NULL ,
db_owner VARCHAR(3) NOT NULL ,
db_accessadmin VARCHAR(3) NOT NULL ,
db_securityadmin VARCHAR(3) NOT NULL ,
db_ddladmin VARCHAR(3) NOT NULL ,
db_datareader VARCHAR(3) NOT NULL ,
db_datawriter VARCHAR(3) NOT NULL ,
db_denydatawriter VARCHAR(3) NOT NULL ,
db_denydatareader VARCHAR(3) NOT NULL ,
db_sysadmin VARCHAR(3) NOT NULL ,
db_serveradmin VARCHAR(3) NOT NULL ,
db_processadmin VARCHAR(3) NOT NULL ,
db_dbcreator VARCHAR(3) NOT NULL ,
Cur_Date DATETIME NOT NULL
DEFAULT GETDATE()
)
DECLARE @dbname NVARCHAR(200)
DECLARE @mSQL1 NVARCHAR(MAX)
DECLARE DBName_Cursor CURSOR
FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ( 'mssecurity', 'tempdb' )
ORDER BY name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @mSQL1 = 'Insert into #DBROLES ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,db_sysadmin,db_serveradmin,db_processadmin,db_dbcreator,
db_denydatareader, db_denydatawriter)
SELECT ' + '''' + @dbname + '''' + ' as DBName ,UserName, ' + CHAR(13)
+ '
Max(CASE RoleName WHEN ''db_processadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_processadmin,
Max(CASE RoleName WHEN ''db_dbcreator'' THEN ''Yes'' ELSE ''No'' END) AS db_dbcreator,
Max(CASE RoleName WHEN ''db_serveradmin'' THEN ''Yes'' ELSE ''No'' END) AS db_serveradmin,
Max(CASE RoleName WHEN ''db_sysadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_sysadmin,
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbname + '.dbo.sysmembers a ' + CHAR(13) + ' join ' + @dbname
+ '.dbo.sysusers b ' + CHAR(13) + ' on a.memberuid = b.uid join '
+ DB_NAME() + '.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
-- Remove the comments below if you want to execute this!
--EXECUTE (@mSql1)
PRINT @msql1
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go
--select * from #DBROLES
--Drop Table #DBROLES
got the error while executing
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #1: Cannot find data type SYSNAME.
February 24, 2014 at 7:07 am
I asked on your other thread but got no answer, is your instance case-sensitive?
What's the exact version of SQL Server that you're working with?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2014 at 7:08 am
Just ran that and it's good on mine. At a guess I would say there is possible some reserved word or an iffy named table?
I will have to pass over to the more experienced on here (Chris M Maybe :cool:)
February 24, 2014 at 7:10 am
iam working on SQl 2005 enterprise .
February 24, 2014 at 7:12 am
GilaMonster (2/24/2014)
I asked on your other thread but got no answer, is your instance case-sensitive?What's the exact version of SQL Server that you're working with?
i.e. version, service pack, edition, etc (SELECT @@version if unsure)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply