Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Users And Associated Database Roles


Database Users And Associated Database Roles

Author
Message
LSumuri
LSumuri
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 86
Comments posted to this topic are about the item Database Users And Associated Database Roles
Francisco-124387
Francisco-124387
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 102
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!
LSumuri
LSumuri
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 86
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.
Ritesh Medhe
Ritesh Medhe
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 234
cool
Ritesh Medhe
Ritesh Medhe
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 234
just a small thing make use of table variable instaed of temp tables. or local temp table
Ritesh Medhe
Ritesh Medhe
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 234
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!!!!!!
mstjean
mstjean
SSC-Addicted
SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)

Group: General Forum Members
Points: 485 Visits: 2542
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
LSumuri
LSumuri
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 86
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.
mstjean
mstjean
SSC-Addicted
SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)

Group: General Forum Members
Points: 485 Visits: 2542
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
YSLGuru
YSLGuru
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1142 Visits: 1665
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!
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