Syslogins, database logins script.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Syslogins, database logins script.

Author

Topic

MediaPirate
Yak Posting Veteran

52 Posts

Posted - 2004-12-15 : 13:01:44

Does anyone have a script already which pulls all the logins and what databases they have public/dbo access to? I'm looking for something more than just pulling syslogins/users and their default dbname, I need a list of all the databases they have dbo access to.

Thanks, Jim

TimS
Posting Yak Master

198 Posts

Posted - 2004-12-15 : 15:01:24

Quick\slow and Dirty answer; I re-wrote my sproc from memory when I did this for a single db as function & gave an un-document sproc call of sp_MSforeachdb to use it.

Tim S

edited minor code bug; fixed logic flaw and added real test code.

IF OBJECT_ID('dbo.ufn_dbo_list') IS NOT NULL DROP FUNCTION dbo.ufn_dbo_list
GO
CREATE FUNCTION dbo.ufn_dbo_list ()
RETURNS
@retArray TABLE (dbname VARCHAR(64), username VARCHAR(64), loginname VARCHAR(64), PRIMARY KEY (dbname, username) )
AS
BEGIN

DECLARE @uid_list TABLE (uid INT PRIMARY KEY, sid VARBINARY(85) )

INSERT INTO @uid_list (uid, sid)
select usr.uid, usr.sid
FROM sysusers usr
LEFT JOIN @uid_list ul2 ON usr.uid = ul2.uid
where ul2.uid IS NULL AND usr.name IN ('dbo', 'db_owner')

WHILE 0 = 0
BEGIN
INSERT INTO @uid_list (uid, sid)
SELECT usr.uid, usr.sid
FROM sysusers usr
JOIN @uid_list ul1 ON usr.uid = ul1.uid
LEFT JOIN @uid_list ul2 ON usr.uid = ul2.uid
WHERE ul2.uid IS NULL

INSERT INTO @uid_list (uid, sid)
SELECT memberuid, usr.sid
FROM sysmembers mem
JOIN sysusers usr ON mem.memberuid = usr.uid
JOIN @uid_list ul1 ON mem.groupuid = ul1.uid
LEFT JOIN @uid_list ul2 ON mem.memberuid = ul2.uid
WHERE ul2.uid IS NULL

IF @@ROWCOUNT = 0 BREAK;
END

INSERT INTO @retArray (dbname, username, loginname)
SELECT DB_NAME() as dbname, USER_NAME(uid) as username, SUSER_SNAME(sid) as loginname
FROM @uid_list
WHERE USER_NAME(uid) NOT IN ('dbo','db_owner')

INSERT INTO @retArray (dbname, username, loginname)
SELECT name, 'dbo', SUSER_SNAME(sid) as loginname
from master.dbo.sysdatabases
WHERE dbid = DB_ID()

RETURN

END
GO

/*

exec sp_addrole @rolename = 'testrole1'

EXEC sp_addrolemember @rolename = 'db_owner' , @membername = 'testrole1'

exec sp_addrole @rolename = 'testrole2'

EXEC sp_addrolemember @rolename = 'testrole1' , @membername = 'testrole2'

EXEC master.dbo.sp_addlogin @loginame = 'testdummy'

EXEC sp_grantdbaccess @loginame = 'testdummy'

EXEC sp_addrolemember @rolename = 'testrole2' , @membername = 'testdummy'

CREATE TABLE #retArray (dbname VARCHAR(64), username VARCHAR(64), loginname VARCHAR(64), PRIMARY KEY (dbname, username) )
EXEC sp_MSforeachdb @command1 = 'USE ?; IF OBJECT_ID(''dbo.ufn_dbo_list'') IS NOT NULL INSERT INTO #retArray (dbname, username, loginname) SELECT dbname, username, loginname FROM dbo.ufn_dbo_list()', @replacechar ='?'
SELECT * FROM #retArray

DROP TABLE #retArray

*/

GO

TimS
Posting Yak Master

198 Posts

Posted - 2004-12-15 : 15:59:06

Code Clean up; sorry about the mistakes been up since yesterday 9:00 AM

IF OBJECT_ID('dbo.ufn_dbo_list') IS NOT NULL DROP FUNCTION dbo.ufn_dbo_list
GO
CREATE FUNCTION dbo.ufn_dbo_list ()
RETURNS
@retArray TABLE (dbname VARCHAR(64), username VARCHAR(64), loginname VARCHAR(64), PRIMARY KEY (dbname, username) )
AS
BEGIN

DECLARE @uid_list TABLE (uid INT PRIMARY KEY, sid VARBINARY(85) )

INSERT INTO @uid_list (uid, sid)
select usr.uid, usr.sid
FROM sysusers usr
LEFT JOIN @uid_list ul2 ON usr.uid = ul2.uid
where ul2.uid IS NULL AND usr.name IN ('dbo', 'db_owner')

WHILE 0 = 0
BEGIN

INSERT INTO @uid_list (uid, sid)
SELECT memberuid, usr.sid
FROM sysmembers mem
JOIN sysusers usr ON mem.memberuid = usr.uid
JOIN @uid_list ul1 ON mem.groupuid = ul1.uid
LEFT JOIN @uid_list ul2 ON mem.memberuid = ul2.uid
WHERE ul2.uid IS NULL

IF @@ROWCOUNT = 0 BREAK;
END

INSERT INTO @retArray (dbname, username, loginname)
SELECT DB_NAME() as dbname, USER_NAME(ul.uid) as username, SUSER_SNAME(ul.sid) as loginname
FROM @uid_list ul
JOIN sysusers usr ON ul.uid = usr.uid
WHERE USER_NAME(ul.uid) NOT IN ('dbo') AND usr.issqlrole = 0

INSERT INTO @retArray (dbname, username, loginname)
SELECT name, 'dbo', SUSER_SNAME(sid) as loginname
from master.dbo.sysdatabases
WHERE dbid = DB_ID()

RETURN

END
GO

/*

SELECT * FROM sysusers

-- Set up Tests
IF USER_ID ('testrole1' ) IS NULL exec sp_addrole @rolename = 'testrole1'
EXEC sp_addrolemember @rolename = 'db_owner' , @membername = 'testrole1'
IF USER_ID ('testrole2' ) IS NULL exec sp_addrole @rolename = 'testrole2'
EXEC sp_addrolemember @rolename = 'testrole1' , @membername = 'testrole2'
IF SUSER_SID ('testdummy' ) IS NULL EXEC master.dbo.sp_addlogin @loginame = 'testdummy'
IF USER_ID ('testdummy' ) IS NULL EXEC sp_grantdbaccess @loginame = 'testdummy'
EXEC sp_addrolemember @rolename = 'testrole2' , @membername = 'testdummy'

*/

/*

-- Example Use
CREATE TABLE #retArray (dbname VARCHAR(64), username VARCHAR(64), loginname VARCHAR(64), PRIMARY KEY (dbname, username) )
EXEC sp_MSforeachdb @command1 = 'USE ?; IF OBJECT_ID(''dbo.ufn_dbo_list'') IS NOT NULL INSERT INTO #retArray (dbname, username, loginname) SELECT dbname, username, loginname FROM dbo.ufn_dbo_list()', @replacechar ='?'
SELECT * FROM #retArray
DROP TABLE #retArray

*/

/*
-- Cleanup Test Roles & Logins
IF USER_ID ('testrole2' ) IS NOT NULL AND ('testdummy' ) IS NOT NULL
EXEC sp_droprolemember @rolename = 'testrole2' , @membername = 'testdummy'
IF USER_ID ('testdummy' ) IS NOT NULL EXEC sp_revokedbaccess @name_in_db = 'testdummy'
IF SUSER_SID ('testdummy' ) IS NOT NULL EXEC master.dbo.sp_droplogin @loginame = 'testdummy'
IF USER_ID ('testrole1' ) IS NOT NULL AND USER_ID ('testrole2' ) IS NOT NULL
EXEC sp_droprolemember @rolename = 'testrole1' , @membername = 'testrole2'
IF USER_ID ('testrole2' ) IS NOT NULL EXEC sp_droprole @rolename = 'testrole2'
IF USER_ID ('testrole1' ) IS NOT NULL EXEC sp_droprolemember @rolename = 'db_owner' , @membername = 'testrole1'
IF USER_ID ('testrole1' ) IS NOT NULL EXEC sp_droprole @rolename = 'testrole1'

*/

GO

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-12-15 : 23:03:35

I like the post and played with it a bit but its missing the NT Id's I tried manipulating the where clause and it expanded the results but what's missing?:

INSERT INTO @uid_list (uid, sid)
select usr.uid, usr.sid
FROM sysusers usr
LEFT JOIN @uid_list ul2 ON usr.uid = ul2.uid
where ul2.uid IS NULL --AND usr.name IN ('dbo', 'db_owner')

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-16 : 00:27:31

--@membername - loginame or 'ALL'
--must be run on respective database

create FUNCTION fnGroupMemberShip(@MemberName varchar(100))
returns
@RtnValue table(GroupID int,GroupName varchar(100),MemberID int,MemberName varchar(100))
as
begin

declare @temp table(memberid int,membername nvarchar(100),groupuid int)
declare @sql nvarchar(4000)

insert into @temp
select usr.uid as MemberID,usr.name as MemberName,mem.groupuid as GroupID
from sysmembers as mem
join sysusers as usr
on mem.memberuid=usr.uid
where (usr.name=isnull(nullif(@membername,'ALL'),usr.name))

insert into @RtnValue select t.groupuid,u.name,t.memberid,t.membername
from @temp as t
join sysusers as u
on t.groupuid=u.uid

return
end

--------------------
keeping it simple...

TimS
Posting Yak Master

198 Posts

Posted - 2004-12-16 : 11:06:26

"but its missing the NT Id's" I am not sure what you mean by nt id's;

Is it missing some data rows?
Or do you wish a addtional column in the data?
If column please give a example of the data
( select statement from the systable if possible)

NOTE: MY query is NOT returning people who are SA of the server.
example of a query to do that below
SELECT loginname FROM master.dbo.syslogins
WHERE IS_SRVROLEMEMBER ( 'sysadmin', loginname ) = 1

Lookup IS_SRVROLEMEMBER in BOL to fine tune that query

Tim S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources