Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Database Users And Associated Database Roles Expand / Collapse
Author
Message
Posted Monday, January 25, 2010 4:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:19 PM
Points: 17, Visits: 86
Comments posted to this topic are about the item Database Users And Associated Database Roles
Post #853371
Posted Tuesday, February 9, 2010 9:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 11:41 AM
Points: 1, Visits: 69
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!
Post #862501
Posted Tuesday, February 9, 2010 3:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:19 PM
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.
Post #862786
Posted Wednesday, February 10, 2010 4:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325, Visits: 234
cool
Post #863083
Posted Wednesday, February 10, 2010 4:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325, Visits: 234
just a small thing make use of table variable instaed of temp tables. or local temp table
Post #863086
Posted Wednesday, February 10, 2010 4:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325, 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!!!!!!
Post #863091
Posted Wednesday, February 10, 2010 1:31 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 10:34 AM
Points: 436, Visits: 2,285
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
Post #863605
Posted Wednesday, February 10, 2010 3:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:19 PM
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.
Post #863690
Posted Wednesday, February 10, 2010 3:56 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 10:34 AM
Points: 436, Visits: 2,285
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
Post #863701
Posted Wednesday, February 17, 2010 2:57 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:51 PM
Points: 889, Visits: 1,550
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!
Post #867549
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse