November 20, 2008 at 3:39 pm
I am trying to script out users,roles, and logins calling SMO from t-sql. I have the sql down but I am having trouble finding the correct smo namespaces to accomplish what I want. This is my first foray into using smo so any help or point in the right dirrection would be much appreciated. I already have found and am searching through http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo(SQL.90).aspx but all these examples are for code and not t-sql.
for logins i just need the login namespace IE. server(" ")
for roles i know the databaserole namespace but i would also like to include which objects this role gives access to.
declare @sql varchar(8000),
@contextvarchar(255),
@objServerint ,
@buffervarchar(8000),
@servername varchar(150)
select @servername = @@servername
select@sql = 'echo. > c:\script.sql'
exec master..xp_cmdshell @sql
-- prepare scripting object
select @context = 'create dmo object'
exec sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
select @context = 'set integrated security '+@servername+''
exec sp_OASetProperty @objServer, LoginSecure, 1
select @context = 'connect to server '+@servername+''
exec sp_OAMethod @objServer , 'Connect', NULL, @servername
exec sp_OAMethod @objServer, 'databases("database name").databaseroles("database role").script' , @buffer OUTPUT, '69' , 'c:\temp.sql'
select @sql = 'type c:\temp.sql >> c:\script.sql'
exec master..xp_cmdshell @sql
and for users i would like script to have user as well as thier role memberships
declare @sql varchar(8000),
@contextvarchar(255),
@objServerint ,
@buffervarchar(8000),
@servername varchar(150)
select @servername = @@servername
select@sql = 'echo. > c:\script.sql'
exec master..xp_cmdshell @sql
-- prepare scripting object
select @context = 'create dmo object'
exec sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
select @context = 'set integrated security '+@servername+''
exec sp_OASetProperty @objServer, LoginSecure, 1
select @context = 'connect to server '+@servername+''
exec sp_OAMethod @objServer , 'Connect', NULL, @servername
exec sp_OAMethod @objServer, 'databases("databasename").users("user").script' , @buffer OUTPUT, '69' , 'c:\temp.sql'
select @sql = 'type c:\temp.sql >> c:\script.sql'
exec master..xp_cmdshell @sql
November 22, 2008 at 1:54 pm
Robert klimes (11/20/2008)
I am trying to script out users,roles, and logins calling SMO from t-sql. I have the sql down but I am having trouble finding the correct smo namespaces to accomplish what I want. This is my first foray into using smo so any help or point in the right dirrection would be much appreciated. I already have found and am searching through http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo(SQL.90).aspx but all these examples are for code and not t-sql.
That's because hardly anyone calls SMO from COM, let alone from SQL through COM. SMO was really meant for client code use, and you may find it difficult to use in this approach. You will also find that for a large number of these things (maybe as much as 50%), it would be easier to just build the scripts yourself from the system catalog views. (The notable exception to this are the table scripts which are notoriously difficult to build on your own).
for logins i just need the login namespace IE. server(" ")
it's "server.Logins". Well, that's what it is in client code.
for roles i know the databaserole namespace but i would also like to include which objects this role gives access to.
This is not a collection, instead you have to EnumObjectPermissions and loop over the enumerator. Not sure how you do this from
T-SQL through OA.
and for users i would like script to have user as well as thier role memberships
As above, you have to use the EnumRoles method. In this case, since Role membership has not other options or attributes, you would almost certainly find it easier to just build this script yourself from the system catalogs.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 27, 2008 at 8:42 am
Thanks Rbarry. the server.logins namespace worked correctly. I will look into the enumerator object but if I cannot figure it out i will do it through sql. the reason i wanted to do it through smo is I have a script obtained from http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html and it was missing these three objects and I wanted to keep this script consitent. I guess in the end, its the result that matters more and not how it got there. Thanks again.
November 27, 2008 at 10:54 am
The problem with using that DMO code (or any DMO code) to script out a 2005 database, is that it will miss everything that is new with 2005. For instance, CLR procs, CLR triggers, CLR UDT's, etc. are probably missed. And Service Broker entities (Contracts, Services, etc.) certainly are. The security model is extensively different and DMO scripting is unlikely to to pickup on things like the seperation of Schemas and Owners (i.e., an object can be in the "dbo' schema, but owned by somebody else).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply