April 17, 2014 at 11:16 pm
Hi ,
i need to add column login type in the output as per the below script , can any one help on this
CREATE TABLE #DBROLES
(
DBName sysname NOT NULL ,
userName VARCHAR(3)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
April 18, 2014 at 4:32 am
Sorry, I don't understand what you are after.
We need at least an example of the expected output.
-- Gianluca Sartori
April 18, 2014 at 5:58 am
Are you looking for the difference between a SQL login and a Windows login? Or something else?
April 19, 2014 at 10:18 am
I need to display the output with the additional column "login type as windows \sql login "in the output after executing the given query .
can any one provide by mofifing the query by adding the login type .
April 21, 2014 at 4:40 am
The column you are looking for is type_desc in sys.server_principals.
April 21, 2014 at 9:13 am
yes , can you please modify the script by adding this and help .
im not expert in scripting
April 21, 2014 at 10:34 am
Not immediately. I'm in the middle of my own urgent project. But I will work on it as I have time. In the meantime, if you know how to do a JOIN, all you have to do is JOIN sysusers to sys.server_principals on name = name.
Since you say you're not a scripting expert, may I ask where you got this script? (Internet or coworker). Have you reverse-engineered it so that you know what it's doing and why it works?
If not, that's the first place you should start. You do not want to be stuck supporting a script you don't understand. It gets messy.
April 21, 2014 at 10:45 am
i googled this script.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply