August 19, 2011 at 7:12 am
i want script to know all logins with with there server roles, database and object level permisions
August 19, 2011 at 8:01 am
See if this gives you the information you seek.
sp_helplogins
For your information it is located in the Master database.
August 19, 2011 at 9:29 am
sp_helplogins will give logins with server level permissions only........... i want to know what permission's they have at database level and object level. (with single stored procedure ...... i want all the information). i want to put that in a table for reference ...........
bitbucket-25253 (8/19/2011)
See if this gives you the information you seek.sp_helplogins
For your information it is located in the Master database.
August 19, 2011 at 9:31 am
sp_helplogins will give logins with server level permissions only........... i want to know what permission's they have at database level and object level. (with single stored procedure ...... i want all the information). i want to put that in a table for reference ...........
August 19, 2011 at 9:45 am
ravishankar.yedoti (8/19/2011)
sp_helplogins will give logins with server level permissions only........... i want to know what permission's they have at database level and object level. (with single stored procedure ...... i want all the information). i want to put that in a table for reference ...........
this seems to be a really common question lately... this is how I do it.. no warranties, dont claim it's perfect, but it works for me... please let me know if it works for you too:
/**********SET RESULTS TO TEXT TO ENSURE FULL OUTPUT******/
/**
NOTE: This script should be run once prior to a database restore to generate the
appropriate permissions scripts. Set results to text and retain them
Once the database is restored, the script must be run against the restored database,
AGAIN, the results should be output to text. These results will include "drop schema"
and "drop user" statements which must be copied and run against the restored database
to drop the production users.
so, you will run the script, restore the database, run the output of first script,
then run the output of that script again... it's simpler than it sounds...**/
/*** NOTE- THIS SCRIPT RETAINS PERMISSONS BUT DOES NOT REPAIR ORPHANED USERS
YOU MUST STILL RUN SP_CHANGE_USERS_LOGIN TO REPAIR ORPHANED USERS
***/
SET NOCOUNT ON
print'--paste these results into new query window and run with results to text,'
print'--then execute the drop statement output again'
print'--permissions script for' +db_name()+' on '+@@servername
print ' '
if ((select COUNT (name) from sys.database_principals where type='R' and is_fixed_role =0 and name!='public') >0)
begin
print '--recreate any user created roles'
select 'create role ['+name+'] authorization [dbo]' from sys.database_principals where type='R' and is_fixed_role =0 and name!='public'
end
else
begin
print '--no user created roles to script'
end
print 'go'
print'--grant users access'
SELECT 'EXEC [sp_grantdbaccess] @loginame =['+[master].[dbo].[syslogins].[loginname]+'], @name_in_db =['+
[sysusers].[name]+']'
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
--WHERE [sysusers].[name]
print 'go'
PRINT '--add users to roles'
select 'EXEC sp_addrolemember ' + '@rolename=['+r.name+ '], @membername= ['+ m.name+']'
from sys.database_role_members rm
join sys.database_principals r on rm.role_principal_id = r.principal_id
join sys.database_principals m on
rm.member_principal_id = m.principal_id
where m.name!='dbo'
order by r.name, m.name
print 'go'
print '--object level perms'
select p.state_desc + ' ' + p.permission_name + ' ON [' + s.name +'].['+ o.name collate Latin1_general_CI_AS+ '] TO [' + u.name collate Latin1_general_CI_AS + ']' from sys.database_permissions p inner join sys.objects o on p.major_id = o.object_id inner join sys.schemas s on s.schema_id = o.schema_id inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
print 'go'
print '--grant databasewide permissions'
select p.state_desc + ' ' + p.permission_name +' TO [' + u.name collate Latin1_general_CI_AS + ']' from sys.database_permissions p inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
where p.class_desc='DATABASE'
print 'go'
PRINT'--list of users and schemas to drop'
PRINT' '
select 'select ''drop schema[''+name+'']'' from sys.schemas where name not in('
select ''''+name+''',' from sys.schemas
PRINT '''dropusername'')'
select 'select ''drop user[''+name+'']'' from sysusers where name not in('
select ''''+name+''',' from sysusers
PRINT '''dropusername'')'
--grant execute to db_executortest
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply