March 16, 2010 at 10:19 am
Hi Folks
Still fumbling my way around these tables. 🙂
In SSMS under Security>Logins>User Mapping you can see a list of databses a particular login has access to.
Can someone tell me what table or tables this info is kept in.
Is one of the tables syslogins and joind to some other table ?
Thanks
Jim
March 17, 2010 at 7:21 am
If you need to test to see if the current user has access to a database, use HAS_DBACCESS.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 10:02 am
Hi,
Try sp_helplogins 'loginname'
[font="Verdana"]Renuka__[/font]
March 17, 2010 at 10:55 am
Thanks
will try it out
Jim
March 17, 2010 at 10:56 am
Thanks
will give it a shot
Jim
March 17, 2010 at 11:29 am
Hi Folks
the info provided is fine for one database or one login, but
what I am trying to accomplish is to pull all the
loginnames from syslogins and join them to whatever table it is to pull out their associated database names, that they have access to.
Any ideas or advice appreciated
Thanks
Jim
March 17, 2010 at 10:28 pm
DECLARE @command VARCHAR(MAX),
@script VARCHAR(MAX);
SET @command =
'
SELECT db_name = ''$DBNAME'',
login_name = SP.name COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
login_type = SP.type_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
user_name = DP.name COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
user_type = DP.type_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
FROM sys.server_principals SP
JOIN $DBNAME.sys.database_principals DP
ON DP.sid = SP.sid
JOIN $DBNAME.sys.database_permissions PERM
ON PERM.grantee_principal_id = DP.principal_id
WHERE PERM.permission_name = N''CONNECT''
AND PERM.state_desc IN (N''GRANT'', N''GRANT_WITH_GRANT_OPTION'')
AND DP.type_desc IN (N''SQL_USER'', N''WINDOWS_USER'', N''WINDOWS_GROUP'')
';
SELECT @script =
STUFF
(
(
SELECT CHAR(13) + CHAR(10) +
'UNION ALL' +
CHAR(13) + CHAR(10) +
REPLACE(@command, '$DBNAME', QUOTENAME(name))
FROM sys.databases
FOR XML PATH(''), TYPE
).value('/text()[1]', 'VARCHAR(MAX)')
,1 , 13, SPACE(0)
) + CHAR(13) + CHAR(10) + 'ORDER BY db_name, login_name;';
PRINT @script;
EXECUTE (@script);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 10:41 pm
Here is a script I have used in the past. Check it out.
http://www.mssqltips.com/tip.asp?tip=1818
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2010 at 10:44 pm
Paul White (3/17/2010)
DECLARE @command VARCHAR(MAX),
@script VARCHAR(MAX);
SET @command =
'
SELECT db_name = ''$DBNAME'',
login_name = SP.name COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
login_type = SP.type_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
user_name = DP.name COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
user_type = DP.type_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
FROM sys.server_principals SP
JOIN $DBNAME.sys.database_principals DP
ON DP.sid = SP.sid
JOIN $DBNAME.sys.database_permissions PERM
ON PERM.grantee_principal_id = DP.principal_id
WHERE PERM.permission_name = N''CONNECT''
AND PERM.state_desc IN (N''GRANT'', N''GRANT_WITH_GRANT_OPTION'')
AND DP.type_desc IN (N''SQL_USER'', N''WINDOWS_USER'', N''WINDOWS_GROUP'')
';
SELECT @script =
STUFF
(
(
SELECT CHAR(13) + CHAR(10) +
'UNION ALL' +
CHAR(13) + CHAR(10) +
REPLACE(@command, '$DBNAME', QUOTENAME(name))
FROM sys.databases
FOR XML PATH(''), TYPE
).value('/text()[1]', 'VARCHAR(MAX)')
,1 , 13, SPACE(0)
) + CHAR(13) + CHAR(10) + 'ORDER BY db_name, login_name;';
PRINT @script;
EXECUTE (@script);
Nice script Paul.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 18, 2010 at 1:10 pm
Thanks Paul
will check it out
jim
March 18, 2010 at 1:11 pm
will take a look
thanks
jim
March 18, 2010 at 2:50 pm
Hi Paul
i am getting this error when trying to exeute the code
1> DECLARE @command VARCHAR(MAX),
2> @script VARCHAR(MAX);
3>
4> SET @command =
5> '
6~ SELECT db_name = ''$DBNAME'',
7~ login_name = SP.name COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
8~ login_type = SP.type_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
9~ user_name = DP.name COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,
10~ user_type = DP.type_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
11~ FROM sys.server_principals SP
12~ JOIN $DBNAME.sys.database_principals DP
13~ ON DP.sid = SP.sid
14~ JOIN $DBNAME.sys.database_permissions PERM
15~ ON PERM.grantee_principal_id = DP.principal_id
16~ WHERE PERM.permission_name = N''CONNECT''
17~ AND PERM.state_desc IN (N''GRANT'', N''GRANT_WITH_GRANT_OPTION'')
18~ AND DP.type_desc IN (N''SQL_USER'', N''WINDOWS_USER'', N''WINDOWS_GROUP'')
19~ ';
20>
21> SELECT @script =
22> STUFF
23> (
24> (
25> SELECT CHAR(13) + CHAR(10) +
26> 'UNION ALL' +
27> CHAR(13) + CHAR(10) +
28> REPLACE(@command, '$DBNAME', QUOTENAME(name))
29> FROM sys.databases
30> FOR XML PATH(''), TYPE
31> ).value('/text()[1]', 'VARCHAR(MAX)')
32> ,1 , 13, SPACE(0)
33> ) + CHAR(13) + CHAR(10) + 'ORDER BY db_name, login_name;';
34>
35> PRINT @script;
36> EXECUTE (@script);
Msg 1934, Level 16, State 1, Server NSAB-SS80-SQL-N, Line 21
SELECT failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on
computed columns and/or query notifications and/or xml data type methods.
1>
any idea on what i am doing wrong ?
Jim
March 18, 2010 at 2:52 pm
question:
is there a way to spool the output to a file so i do not have to copy and paste it into a file ?
Thanks
Jim
March 18, 2010 at 3:02 pm
JC-3113 (3/18/2010)
question:is there a way to spool the output to a file so i do not have to copy and paste it into a file ?
Thanks
Jim
Is this for the script from Paul, or the one in the link I provided?
For the one I provided, I save the output to an html file.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 18, 2010 at 3:09 pm
on Paul's i get an error
the linked one works
how do i save it to a file ?
Thanks
Jim
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply