June 4, 2003 at 8:59 am
Hi,
I'm currently writing a SQL-DMO application that is reverse engineering all objects on a given server (DBs, Tables, views, users, .....).(Lot of fun with SQL-DMO 😉 )
But I do have a problem while trying to script the role membership of the logins or users.
I already can script the logins created on the server... but without server role info.
I can also script the databases users and the given object permissions.... but not the database roles (db_datareader,...)
My goal would be to produce something like what you can do by using the 'Generate SQL script' feature of SQL EM (Launch application, don't choose any objects and simply check the box "script database users & database roles" in the "options" tab)
Can someone please put me on the track ?
Thanks in advance.
Igor
June 6, 2003 at 1:29 am
try this
oserver.databases("dbname").DatabaseRoles("rolname").Script
you could use this in a loop
June 6, 2003 at 1:38 am
Thanks Jurgen.
But if I'm not wrong this command will script the roles : sp_addrole.....
The kind of script I would like to obtain would look like :
if not exists (select * from dbo.sysusers where name = N'xxxxxx' and uid < 16382)
EXEC sp_grantdbaccess N'xxxxxx', N'xxxxxx'
GO
exec sp_addrolemember N'db_datareader', N'xxxxxx'
GO
exec sp_addrolemember N'db_datawriter', N'xxxxxx'
GO
No problem to get the script of sp_grantdbaccess. The trick is to get the "sp_addrolemember".
Regards
Igor
June 6, 2003 at 2:20 am
I gues you have to write that part yourself by using eg
oserver.Databases("dbname").DatabaseRoles("rolname").EnumDatabaseRoleMember
This returns a queryresult which can be easily read (loop through rows).
Regards,
Jurgen
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply