Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting full userlist from SQL. Expand / Collapse
Author
Message
Posted Friday, December 05, 2003 5:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 05, 2003 12:00 AM
Points: 5, Visits: 1
Anyone know how to obtain a full userlist from a SQL Database with full permissions.

There must be some query you can run through the Analyser.

Any suggestions would really help.

Many thanks




Post #18870
Posted Friday, December 05, 2003 6:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
What about sp_helprotect or

CREATE PROCEDURE sp_Permissions
AS

SET NOCOUNT ON

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE [name] LIKE '#perm%')
DROP TABLE #perm

CREATE TABLE #perm (
[qid] [int] IDENTITY (1,1) NOT NULL,
[user] [int] NOT NULL,
[inherfrom] [int] NOT NULL,
PRIMARY KEY (
[user],
[inherfrom]
)
)

INSERT INTO #perm ([user], [inherfrom]) SELECT [uid], [uid] FROM sysusers
WHERE issqlrole = 0 AND hasdbaccess = 1 and uid != 1

WHILE EXISTS (SELECT
p1.[user],
groupuid
FROM
sysmembers
INNER JOIN
#perm p1
ON
p1.[inherfrom] = sysmembers.memberuid
WHERE
groupuid NOT IN (
SELECT
inherfrom
FROM
#perm p2
WHERE
p2.[user] = p1.[user]
)
)
BEGIN
INSERT INTO #perm ([user], [inherfrom])
SELECT
p1.[user],
groupuid
FROM
sysmembers
INNER JOIN
#perm p1
ON
p1.[inherfrom] = sysmembers.memberuid
WHERE
groupuid NOT IN (
SELECT
inherfrom
FROM
#perm p2
WHERE
p2.[user] = p1.[user]
)
END

--Check permissions for the user from all inheritance paths.

SELECT
u2.[name] AS UserName,
u1.[name] AS InheritesVia,
CASE xtype
WHEN 'U' THEN 'Table'
WHEN 'V' THEN 'View'
WHEN 'S' THEN 'System'
WHEN 'P' THEN 'Procedure'
WHEN 'FN' THEN 'Function'
END AS ObjectType,
sysobjects.[name] AS Object,
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 1) = 1 THEN 'Granted'
WHEN (actmod & 1) = 1 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [SELECT],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 8) = 8 THEN 'Granted'
WHEN (actmod & 8) = 8 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [INSERT],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 2) = 2 THEN 'Granted'
WHEN (actmod & 2) = 2 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [UPDATE],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 16) = 16 THEN 'Granted'
WHEN (actmod & 16) = 16 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [DELETE],
CASE WHEN xtype IN ('P','FN') THEN
CASE
WHEN (actadd & 32) = 32 THEN 'Granted'
WHEN (actmod & 32) = 32 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [EXEC],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 4) = 4 THEN 'Granted'
WHEN (actmod & 4) = 4 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [DRI]
FROM
syspermissions
INNER JOIN
#perm
INNER JOIN
sysusers u1
ON
u1.uid = [inherfrom]
INNER JOIN
sysusers u2
ON
u2.uid = [user]
ON
[inherfrom] = grantee
INNER JOIN
sysobjects

ON
sysobjects.[id] = syspermissions.[id]
ORDER BY
[UserName],
[ObjectType],
[Object]

DROP TABLE #perm
GO


Frank
http://www.insidesql.de
http://www.familienzirkus.de

Edited by - Frank kalis on 12/05/2003 06:14:34 AM


--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Post #89972
Posted Friday, December 05, 2003 7:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 05, 2003 12:00 AM
Points: 5, Visits: 1
This doesn't appear to work correctly.

I'm basically after a list of users that have been created under SECURITY|LOGINS and what Databases they have access to and what permissions.

any ideas?




Post #89973
Posted Friday, December 05, 2003 7:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
So, more something like sp_helplogins ?

Frank
http://www.insidesql.de
http://www.familienzirkus.de


--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Post #89974
Posted Friday, December 05, 2003 7:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 23, 2010 1:55 PM
Points: 55, Visits: 7
sp_helplogins
sp_helpuser
sp_helprotect




Post #89975
Posted Friday, December 05, 2003 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 05, 2003 12:00 AM
Points: 5, Visits: 1
Thanks for that, worked perfectly.




Post #89976
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse