April 12, 2010 at 11:12 pm
Hi,
I am in need of T-Sql script for generating the user ,login and their roles from sql server 2005 server. can you post your script or give the references from where i can download the script ?
Thanks in Advance.
April 13, 2010 at 1:18 am
hi,
I think the following will give help u .....
http://www.sqlservercentral.com/articles/Administration/listofdatabaseuserswithdatabaseroles/1545/
cheers!:-)
Sasidhar Chowdary
April 13, 2010 at 4:48 am
You can also try this one, quite uesful for SQL accounts as copies out the sid and the hashed password.
Doesnt do server roles though.
http://support.microsoft.com/kb/918992/
Adam Zacks-------------------------------------------Be Nice, Or Leave
April 13, 2010 at 5:11 am
Here, if any user dont have any database roles but it has select permissions on some tables and execute permissions on some stored procedures...How to retrive that information ?
Thank You.
Regards,
Raghavender Chavva
April 13, 2010 at 8:56 am
Raghavender (4/13/2010)
Here, if any user dont have any database roles but it has select permissions on some tables and execute permissions on some stored procedures...How to retrive that information ?
Run this:
SELECT --rm.role_principal_id,
'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''')
+ ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS '--Role Memberships'
FROM sys.database_role_members AS rm
ORDER BY rm.role_principal_id
--Object Level Permissions'
SELECT
CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +
perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(Schema_NAME(obj.schema_id)) + '.'
+ QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
ORDER BY usr.name
--Database Level Permissions'
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE
--usr.name = @OldUser
--AND
perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
It runs at database level and provides 3 outputs. Hope it helps.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply