SQL Server login access rights

  • 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,

  • you can use sp_helpuser

    exec sp_helpuser 'tester'

    results:

    UserName GroupName LoginName DefDBNameDefSchemaNameUserIDSID

    tester db_datareader NULL NULLdbo 45 0xF18F1E5833F5B74B8AED3994FE5CD801

    tester db_denydatawriter NULL NULLdbo 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]

  • 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?

  • 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]

  • 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),

    DBNameVarchar(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]

  • 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

  • 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,

  • 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]

  • 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)

  • 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]

  • 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...........

  • 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