Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

As you guys probably know, SQL Server does not provide an out-of-the-box solution to export all the security related statements that are declared for each element in the database (both at the database level and also the object level)

I'm talking about the ability to come up with this information

  • All the users
  • All the user-defined roles
  • All permissions at database level (e.g. GRANT CREATE FUNCTION)
  • All permissions at the object level (e.g. GRANT SELECT ON OBJECT::xxx)

You would think that SQL Server MUST have something like this but neither the SQL Server Export Wizard or the various scripts that are generated as a result of right clicking the objects do capture this information.

I have seen online many different "possible solutions" using non-curated scripts that people graciously post, but since I have to be 100% sure that all security information is captured I can't fully rely on those scripts.

I have the option of using those as a starting point to write something myself but I hate having to re-invent the wheel for a requirement that you would think many people may have.

Isn't there a tool provided by someone (either as part of the SQL Server product or an 3rd party tool) that could reliably provide you with this information?

Or at least, is there a community supported script that most of people would agree "will do the job"?


share|improve this question

I'm sorry you haven't had a response since yesterday; here's at least a starting point for you.

You can try pulling the pieces you need out of. As always, read the discussion threads (I was, regrettably, unable to find a script endorsed by the big names I recognize, so test thoroughly! Schema, object, server, and column level permissions are often missing).

I haven't actually used any of these, but they're a set of starting points, in no particular order.

List all permission in all databases

Automated Permissions Auditing With Powershell and T-SQL: Part 1

Permission scripting over all databases

Script DB Level Permissions v2.1

Stored procedure to script out user permissions

Security Queries: Database-level

Security Queries: Server-level

share|improve this answer

Idera SQL Permissions Extractor seems to be the product you're looking for.

It can script server and object permissions and it is free. There is also a commercial edition, called SQL Secure which has more features.

The feature comparison between the two editions can be found here.

share|improve this answer

refer that link. if you don't want orphan users to be fixed, remove the last insert that is,

insert into ##temp1
select '
declare @query varchar(1000)
declare @executequery cursor
set @executequery=cursor for
select '' sp_change_users_login  ''+CHAR(39)+''auto_fix''+CHAR(39)
from sysusers
where (issqluser = 1 ) and (sid is not null and sid <> 0x0)
AND SUSER_SNAME(sid) IS NULL and name not in(''dbo'',''guest'',''sys'',''information_schema'')
open @executequery
fetch next from @executequery into @query
while @@fetch_status=0
 exec (@query)
 print (@query)
fetch next from @executequery into @query
close @executequery;
deallocate @executequery;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.