Database Users And Associated Database Roles

  • Comments posted to this topic are about the item Database Users And Associated Database Roles

  • When I try to run this script I am geting the error below:

    Msg 102, Level 15, State 1, Line 14

    Incorrect syntax near '?'.

    It does not make any sense and I cannot find out why this error is being displayed. Has anyone else had this issue? Thanks!

  • The boxes '????' representing spaces/tab, are part of the query being copied over, get rid of them and your query will execute OK.

    For instance, "????SELECT role_principal_id, member_principal_id FROM sys.database_role_members"

    Delete the ????, it may not show in your query window, but just delete tab or space of line inside for it to look like "SELECT role_principal_id, member_principal_id FROM sys.database_role_members".

    Do this for the rest of the line and you should be OK.

  • cool

  • just a small thing make use of table variable instaed of temp tables. or local temp table

  • is it possible to drill down to object level, say particular user have xyz right on abc object. eg: user1 have execute permission on procedure1?

    Thanks in advance!!!!!!

  • You don't need a cursor; run this from the db you want to collect information on.

    SELECT

    sdp2.[name] as DBUser, sdp2.type_desc as DBUserType,

    sdp1.[name] as DBUserRole, sdp1.type_desc as DBUserRoleType

    FROM sys.database_role_members drm

    LEFT JOIN sys.database_principals sdp1 on drm.role_principal_id=sdp1.principal_id

    LEFT JOIN sys.database_principals sdp2 on drm.member_principal_id=sdp2.principal_id

    ORDER BY

    sdp2.name,sdp1.name


    Cursors are useful if you don't know SQL

  • SSC-Enthusiastic, I like the script you posted, it works the same, and importantly I do not have to user a cursor and create a temp table to get the information I want.

    Thanks.

  • Glad you like it.

    By the way, my identity is not "SSC-Enthusiastic", just as yours is not "Forum Newbie".

    Generally you should try to avoid using cursors in tsql-- with few exceptions. There are some real gurus on this site, it's a great resource.


    Cursors are useful if you don't know SQL

  • mstjean (2/10/2010)


    Glad you like it.

    By the way, my identity is not "SSC-Enthusiastic", just as yours is not "Forum Newbie".

    Generally you should try to avoid using cursors in tsql-- with few exceptions. There are some real gurus on this site, it's a great resource.

    DITTO to the avoiding Cursors and Guru comment to.

    When I first looked at this I was like thats cool but whats with the CURSOR? I assumed there must be some klind of restriction disallowing it.

    Larry - Is your background in programming verses DB related?

    Kindest Regards,

    Just say No to Facebook!
  • I initially started off doing SQL Server work after finishing from Uni for a few months, loved it but then was whisked away and told to attend to problem applications, basically, programming code debugging and coding in fixes etc. From there on I have been spending alot more time coding in VB.NET than attending to SQL related tasks. I was really impressed with the SQL Server 05 & 08 when I first checked out the features on them, kinda brought back my interest for it.

    From my work point of view, I would like to utilise SQL Server more in terms of enhancing applications being built instead of it being just a data store, some programmers tend to code everything into thier applications than utilizing what SQL server has to offer. Would we by any chance have some guidelines telling us whats best in terms of either using SQL Server or direct applicating coding when building functionalities and business logic etc.?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply