Re: Script DB Object Level Security with SQL2005

KevinL (KevinL@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I see no option to script roles. This is what I did:
Open Management Studio, expand databases, right click the DB, click
Tasks, then Generate Script. The DB I right clicked is highlighted, I
click next. I verified the Script Options to make sure Script
Object-Level Permissions is True (there is no Option that mentions
Roles) and click next. On Object Types window choices are Schema,
Stored Procedures, Tables, User-defined data types, User-defined
functions and Users.
No matter which option(s) I choose, and I've tried every combination I can
think of, Role level permissions are not scripted.

If "Database roles" are not listed, this would indicate that you don't
have any user-defined roles in the database, only the pre-defined roles,
db_owner and the like. Indeed, it appears that membership in these roles
are not scripted.

You can script all role membership with this SELECT:

SELECT 'EXEC sp_addrolemember ''' + r.name + ''', ''' + u.name + ''''
FROM sys.database_role_members rm
JOIN sys.database_principals u
ON rm.member_principal_id = u.principal_id
JOIN sys.database_principals r
ON rm.role_principal_id = r.principal_id
WHERE u.name <> 'dbo'

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Cannot Open SQL Server Table in Access.ADP File.
    ... Other possiblities would be that you didn't refresh the database window ... I have an SQL Server 2005 database which functions properly with my ... the Generate Script Wizard to recreate the SQL Server Database. ... Then, If I attempt to open another table, it opens. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SQL Security
    ... except I'm having problems making it work in a script. ... ;Set properties of DB objects and open connection to database ... > from Books Online (within the SQL Server program group): ... > communicate with SQL Server. ...
    (microsoft.public.sqlserver.server)
  • RE: Project Server 2003
    ... when I got to the run script step, I ran the script from the copied database ... I was also wondering about the create database ... For example, type Project Server, and then click OK. ... In the left pane of SQL Server Management Studio, expand Databases, ...
    (microsoft.public.windows.server.sbs)
  • Re: Database Access
    ... Do you create the group in SQL Server on the NT Domain? ... >> from a client script to ... > the database server would not be possible from client machines. ... You will want the sqloledb connection string. ...
    (microsoft.public.scripting.jscript)
  • Re: Scripting a SQL database
    ... Virtually everything SQL Server Enterprise Manager does can be ... To run the generated script in ASP, ... You can also create a DTS (Data Transformation Package) ... that you'll want to select the "copy database objects and data" ...
    (microsoft.public.inetserver.asp.db)