Database Users And Associated Database Roles

Question

Post reply

Database Users And Associated Database Roles

LSumuri

SSC Enthusiast

Points: 145
More actions
January 25, 2010 at 4:09 pm

#218524

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

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

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

Francisco-124387 Grasshopper Points: 13 More actions · Answer 1

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 SSC Enthusiast Points: 145 More actions · Answer 2

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 SSCommitted Points: 1941 More actions · Answer 3

Ritesh Medhe

SSCommitted

Points: 1941

February 10, 2010 at 4:29 am

#1116684

cool

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 4

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

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 5

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 Hall of Fame Points: 3310 More actions · Answer 6

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 SSC Enthusiast Points: 145 More actions · Answer 7

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 Hall of Fame Points: 3310 More actions · Answer 8

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 SSC-Insane Points: 21661 More actions · Answer 9

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!

LSumuri SSC Enthusiast Points: 145 More actions · Answer 10

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