SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting full userlist from SQL.


Getting full userlist from SQL.

Author
Message
a5972s
a5972s
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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



Frank Kalis
Frank Kalis
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20151 Visits: 289
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/
a5972s
a5972s
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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?



Frank Kalis
Frank Kalis
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20151 Visits: 289
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/
simon_hyan
simon_hyan
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 Visits: 7
sp_helplogins
sp_helpuser
sp_helprotect



a5972s
a5972s
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 1
Thanks for that, worked perfectly.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search