July 21, 2008 at 8:01 am
Hi Experts,
A bit similar requirement is posted on the forum, but i dont want to interrupt in between. That's why i decided to write a new post.
My requirement is to know which user/login is having what type of rights on server. e.g.
login name: test is having db_datareader,db_datawriter etc rights on DB1,DB2
test1 is having **** on DB2,DB3 etc.
Cheers,
July 21, 2008 at 9:00 am
you can use sp_helpuser
exec sp_helpuser 'tester'
results:
UserName GroupName LoginName DefDBName DefSchemaName UserID SID
tester db_datareader NULL NULL dbo 45 0xF18F1E5833F5B74B8AED3994FE5CD801
tester db_denydatawriter NULL NULL dbo 45 0xF18F1E5833F5B74B8AED3994FE5CD801
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 21, 2008 at 10:25 am
sp_helpuser is not giving the desired result and its not possible to run it if you have huge no of users - to run it for individual user is not possible.
Could i get a script for that?
July 21, 2008 at 11:23 am
I got this below code from sp_helpuser sproc.
This will server your purpose, but you will have to run it on each db seperately.
you can try using "sp_MSForEachdb" sproc to run this in all databases in one query
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
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 21, 2008 at 11:44 am
Actually, here is the complete script for all the databases.
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
CREATE TABLE #tuser (
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),
DBName Varchar(50))
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 name
DROP TABLE #TUser
It is worth noting that sp_MSForEachdb is an undocumented sproc. Basically meaning the results are not guaranteed and use it at your own risk .
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 21, 2008 at 12:10 pm
Hi this is the script which gives details of permissions that an user or role is having to objects and also what users are connected with which roles under a database.
--use
select case when substring(suser_sname(s2.sid),1,40) is null then 'This is a role, not a login.'
else
substring(suser_sname(s2.sid),1,40)
end as LoginName,substring(s3.name,1,25) ObjectName,substring(user_name(s1.uid),1,40)UserName ,
case
when s3.xtype ='C' then 'Check Constraint'
when s3.xtype ='D' then 'Default Constraint'
when s3.xtype ='F' then 'Foregin Key Constraint'
when s3.xtype ='L' then 'Log'
when s3.xtype ='FN' then 'Scalar Function'
when s3.xtype ='IF' then 'Inlilned table-function'
when s3.xtype ='P' then 'Stored Procedure'
when s3.xtype ='PK' then 'Primary Key Constraint'
when s3.xtype ='RF' then 'Replication Stored Procedure'
when s3.xtype ='TF' then 'Table Function'
when s3.xtype ='TR' then 'Trigger'
when s3.xtype ='U' then 'User Table'
when s3.xtype ='S' then 'System Table'
when s3.xtype ='UQ' then 'Unique Constraint'
when s3.xtype ='V' then 'View'
when s3.xtype ='X' then 'Extended Stored Procedure'
end as ObjectType,
case
when s1.action = 178 then 'Create Function'
when s1.action = 193 then 'Select'
when s1.action = 195 then 'Insert'
when s1.action = 196 then 'Delete'
when s1.action = 197 then 'Update'
when s1.action = 198 then 'Create Table'
when s1.action = 203 then 'Create Database'
when s1.action = 207 then 'Create View'
when s1.action = 222 then 'Create Procedure'
when s1.action = 224 then 'Execute'
when s1.action = 233 then 'Create Default'
when s1.action = 236 then 'Create Rule'
when s1.action = 26 then 'References'
end as 'Permissions'
from sysusers s2 inner join sysprotects s1
on s1.uid = s2.uid
inner join sysobjects s3
on s1.id = s3.id
and s3.type not in ('S')
--and s3.name in ('ObjectName')
--and s2.sid =suser_sid('LoginName')
Order by UserName desc
--select suser_sname(sid),* from sysusers where sid is not null
/********** Role permissions details*************************************/
select substring(db_name(),1,30) DatabaseName,substring(suser_sname(s2.sid),1,30) LoginName,substring(user_name(s1.memberuid),1,30) UserName,substring(user_name(s1.groupuid),1,30) Role from sysmembers s1
inner join sysusers s2
on s1.memberuid = s2.uid
where s2.sid is not null
-- and s2.sid = suser_sid('LoginName')
order by UserName,Role desc
July 21, 2008 at 1:41 pm
Shiva/murthykalyani thank you very much for your respond.
Shiva, your script for all the databases is not giving any result don’t know why.
murthykalyani, from your script the below part
select substring(db_name(),1,30) DatabaseName,substring(suser_sname(s2.sid),1,30) LoginName,substring(user_name(s1.memberuid),1,30) UserName,substring(user_name(s1.groupuid),1,30) Role from sysmembers s1
inner join sysusers s2
on s1.memberuid = s2.uid
where s2.sid is not null
-- and s2.sid = suser_sid('LoginName')
order by UserName,Role desc
is giving absolutely fine result for the specific database not for all, is it possible to get result for all the DBs in one go.
cheers,
July 21, 2008 at 2:19 pm
Two things:
1. The script (2nd) was not giving the db name earlier, so had to make a small edit to the script. I wonder if you have picked up the older version.
2. I forgot to mention this earlier, but that script applies only for SQL Server 2005. What version are you using?
2.a. If you are using 2000, you can easily modify murthykalyani’s script to work on all the db’s using sp_MSForEachDB sproc.
I tested this script on a SQL Server 2005 std – sp2, it worked with out any problem.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 21, 2008 at 2:27 pm
Shiva,
I am also using SQL 2005 and again tried your big script, but again no luck. See the result below
(0 row(s) affected)
name GroupName LoginName default_database_name default_schema_name principal_id sid DBName
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------
(0 row(s) affected)
July 21, 2008 at 3:44 pm
My bad!!
There is line in there that says
and name like ''tester''
you have to comment out that specific line, replace it with
/*and name like ''tester'' */
This like of code is used to filter the resultset to a specific user.
and you dont have a user called 'tester' on your server, so getting 0 results.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 21, 2008 at 3:58 pm
Thanks Shiva,
it worked after comment the
--and u.name like ''tester''
line, what could be the harm to run undocumented procedure sp_MSForEachdb
as i have to execute it my no of prod servers...........
July 21, 2008 at 4:07 pm
There is no harm in using it. I've been using that sproc (and the sp_MSForEachTable) for quite a long time with out any problems. But officially MS doesnt support it. So it inherits all the issues any undocumented and unsupported stored procedure would face.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply