November 19, 2008 at 4:40 am
Does any one has a script where it gives all logins the instance , db's associated with it and the server role it has. something like this
Login Database Serverrole
1.MLevan EmpPay datareader,writer,ddladmin
EmpHours db_owner
2.CRiley SIRSDev db_owner
EMpPay datareader,datawriter
SIRSDevelopers datareader,writer,ddladmin
November 19, 2008 at 4:57 am
That would be very handy. I am interested to see if you are successful.:)
November 19, 2008 at 5:41 am
See url below... although it's an application as opposed to actual raw tsql.
http://www.sqlservercentral.com/articles/Administering/dumpsqlpermissions/1314
The application will return the information you require in an excel spreadsheet, although be wary it can take sometime to return said information!
Regards,
Mark
November 19, 2008 at 5:57 am
Mike Levan (11/19/2008)
Does any one has a script where it gives all logins the instance , db's associated with it and the server role it has. something like thisLogin Database Serverrole
1.MLevan EmpPay datareader,writer,ddladmin
EmpHours db_owner
2.CRiley SIRSDev db_owner
EMpPay datareader,datawriter
SIRSDevelopers datareader,writer,ddladmin
use this in the particular db
use master
SELECT * FROM sys.database_principals
WHERE TYPE = 's'
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 19, 2008 at 6:06 am
that's not what he requires
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 19, 2008 at 6:10 am
The first part, scripting the logins themselves, is covered in this KB article. You'll need to add the two stored procedures and run sp_help_revlogin to export the logins.
How to transfer the logins and the passwords between instances of SQL Server 2005
The rest I'll try and get up if someone else doesn't get them first.
K. Brian Kelley
@kbriankelley
November 19, 2008 at 6:24 am
I am not sure how this works nor am not supposed to install any 3rd party applications.
Let me know where i can do it through T-SQL. I was expecting there may be some ready scripts some one has used already such things. Lets see if any members comes up with a script, that would really be gr8 and make my life easier.
Thanks to everyone responding to my post
November 19, 2008 at 7:54 am
November 19, 2008 at 8:00 am
Mike
Have you seen the discussion for this post? It looks like a few people have experienced problems.
http://www.sqlservercentral.com/Forums/Topic402799-619-1.aspx
Might be worthwhile joining that discussion? As the original author of the script may see the post.
November 19, 2008 at 8:26 am
That script is working for me if using the one altered by Ryan Austin
can give a try.
In his script what does he mean AD Login, windows login in my case.
November 20, 2008 at 6:34 am
Hi ,
Try this script it works fine on SQL 2005.
It was posted earlier in this forum.
DROP PROC usp_sql_audit
GO
CREATE PROC usp_sql_audit
AS
SET NOCOUNT ON
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
DROP TABLE ##Users;
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
DROP TABLE ##DBUsers;
DECLARE @DBName VARCHAR(32);
DECLARE @SQLCmd VARCHAR(1024);
CREATE TABLE ##Users (
[sid] varbinary(85) NULL,
[Login Name] nvarchar(128) NULL,
[Default Database] sysname NULL,
[Login Type] varchar(9),
[AD Login Type] varchar(8),
[sysadmin] varchar(3),
[securityadmin] varchar(3),
[serveradmin] varchar(3),
[setupadmin] varchar(3),
[processadmin] varchar(3),
[diskadmin] varchar(3),
[dbcreator] varchar(3),
[bulkadmin] varchar(3))
---------------------------------------------------------
INSERT INTO ##Users
SELECT sid,
loginname AS [Login Name],
dbname AS [Default Database],
CASE isntname WHEN 1 THEN 'AD Login' ELSE 'SQL Login' END AS [Login Type],
CASE WHEN isntgroup = 1 THEN 'AD Group' WHEN isntuser = 1 THEN 'AD User' ELSE '' END AS [AD Login Type],
CASE [sysadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [sysadmin],
CASE [securityadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [securityadmin],
CASE [serveradmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [serveradmin],
CASE [setupadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [setupadmin],
CASE [processadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [processadmin],
CASE [diskadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [diskadmin],
CASE [dbcreator] WHEN 1 THEN 'Yes' ELSE 'No' END AS [dbcreator],
CASE [bulkadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [bulkadmin]
FROM master.dbo.syslogins;
---------------------------------------------------------
SELECT [Login Name],
[Default Database],
[Login Type],
[AD Login Type],
[sysadmin],
[securityadmin],
[serveradmin],
[setupadmin],
[processadmin],
[diskadmin],
[dbcreator],
[bulkadmin]
FROM ##Users
ORDER BY [Login Type],[AD Login Type],[Login Name]
CREATE TABLE ##DBUsers (
[Database User ID] nvarchar(1024),
[Server Login] nvarchar(1024),
[Database Role] nvarchar(1024),
[Database] sysname)
DECLARE csrDB CURSOR FOR
SELECT name
FROM master..sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');
OPEN csrDB
FETCH NEXT
FROM csrDB
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLCmd = 'INSERT INTO ##DBUsers
SELECT su.[Name] as [Database User ID],
COALESCE (u.[Login Name], ''**Orphaned**'') as [Server Login],
COALESCE (sug.[name], ''Public'') AS [Database Role],
''' + @DBName + ''' as [Database]
FROM [' + @DBName + '].[dbo].[sysusers] su
LEFT OUTER JOIN ##Users u
ON su.sid = u.sid
LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm
INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug
ON sm.groupuid = sug.uid)
ON su.uid = sm.memberuid
WHERE su.hasdbaccess = 1
AND su.[name] != ''dbo'' '
EXEC (@SQLCmd)
FETCH NEXT
FROM csrDB
INTO @DBName
END
CLOSE csrDB
DEALLOCATE csrDB
SELECT *
FROM ##DBUsers
ORDER BY [Database User ID],[Database];
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
DROP TABLE ##Users;
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
DROP TABLE ##DBUsers;
GO
November 20, 2008 at 8:18 am
Thanks for the script, I like it shows Orphan users too
November 20, 2008 at 8:56 am
Try this:
--http://www.sqlservercentral.com/scripts/Administration/63841/
USE MASTER
GO
BEGIN
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
CREATE TABLE #tuser (
DBName VARCHAR(50),
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name VARCHAR(50) NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
sid VARBINARY(85))
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
''?'' as DBName,
u.name As UserName,
CASE
WHEN (r.uid IS NULL) THEN ''public''
ELSE r.name
END AS GroupName,
l.name AS LoginName,
NULL AS Default_db_Name,
NULL as default_Schema_name,
u.uid,
u.sid
FROM ?.dbo.sysUsers u
LEFT JOIN (?.dbo.sysMembers m
JOIN ?.dbo.sysUsers r
ON m.groupuid = r.uid)
ON m.memberuid = u.uid
LEFT JOIN dbo.sysLogins l
ON u.sid = l.sid
WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
/*and u.name like ''tester''*/
ORDER BY u.name
'
ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb '
SELECT
''?'',
u.name,
CASE
WHEN (r.principal_id IS NULL) THEN ''public''
ELSE r.name
END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM ?.sys.database_principals u
LEFT JOIN (?.sys.database_role_members m
JOIN ?.sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN ?.sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE <> ''R''
/*and u.name like ''tester''*/
order by u.name
'
SELECT *
FROM #TUser
ORDER BY DBName,
[name],
GroupName
DROP TABLE #TUser
END
HTH!
MJ
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply