SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by W Louw / Permission scripting over all databases / Latest Posts

RE: Permission scripting over all databases

Sailor — Thu, 13 Dec 2012 09:10:12 GMT

For the new user, I'd it to generate a create user statement:USE [dbname]GOCREATE USER [mynewuser] FOR LOGIN [domain\firstname.lastname]GOWhere mynewuser is paramater 1 and domain\firstname.lastname is second parameter.

RE: Permission scripting over all databases

Josep — Mon, 06 Feb 2012 09:10:56 GMT

I found that this script doesn't write down schema permissions.You might check the following: http://www.sqlservercentral.com/scripts/Security/71562/

RE: Permission scripting over all databases

Bill Kline-270970 — Fri, 31 Jul 2009 13:34:39 GMT

Great script, but I found a problem.If the ANSI setting CONCAT_NULL_YIELDS_NULL is ON, then I received some records with a NULL value. When I changed the setting to OFF, then the resulting statement was incomplete; i.e., the GRANT statement was missing.Example: --ARS_PROD DELETE ON AREA TO Developer GONotice the 2 spaces between the database name and the permission, with no GRANT statement in between.

RE: Permission scripting over all databases

Telammica — Wed, 08 Jul 2009 08:54:35 GMT

Even if you execute it like this?exec RetrievePermissions 'myUser', '',0

RE: Permission scripting over all databases

FreeHansje — Wed, 08 Jul 2009 08:27:43 GMT

So I ran this script, and nomatter what parms I use('' or 'sa') I get 1 empty output column named textcolumn. How should I execute this?!

RE: Permission scripting over all databases

Anielvarma — Tue, 09 Dec 2008 08:37:35 GMT

It is a great script, however, I found that the scipt omit certain explicit permissions. For example, I run the following exec RetrievePermissions 'test', '',0I get the following results --ReportServerEXEC sp_addrolemember db_datareader, testGO--ReportServerEXEC sp_addrolemember db_datawriter, testGO--ReportServerGRANT EXECUTE ON AddBatchRecord TO TestGO--ReportServer1EXEC sp_addrolemember db_datareader, testGO--ReportServer1GRANT EXECUTE ON AddBatchRecord TO TestGOI used a free tool called SQLPermissions created by Idera that can be downloaded here http://www.idera.com/Products/SQLpermissions/Default.aspxwhich provided me the following T-Sql codeIF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Test')BEGIN CREATE LOGIN [Test] WITH PASSWORD='', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF ALTER LOGIN [Test] EnabledENDUSE [ReportServer]GOIF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Test')CREATE USER [Test] FOR LOGIN [Test] WITH DEFAULT_SCHEMA=[dbo]USE [ReportServer]GOexec sp_addrolemember N'db_datareader', N'Test'GOexec sp_addrolemember N'db_datawriter', N'Test'GOUSE [ReportServer]GOGrant CONNECT ON Database::[ReportServer] TO [Test] GOUSE [ReportServer]GOGrant SHOWPLAN ON Database::[ReportServer] TO [Test] GOUSE [ReportServer]GOGrant VIEW DEFINITION ON Database::[ReportServer] TO [Test] GOUSE [ReportServer]GOGrant EXECUTE ON [dbo].[AddBatchRecord] TO [Test] GOUSE [ReportServer]GOGrant ALTER ON [dbo].[AddModelPerspective] TO [Test] GOUSE [ReportServer]GOGrant TAKE OWNERSHIP ON [dbo].[AddModelPerspective] TO [Test] WITH GRANT OPTION GOUSE [ReportServer1]GOIF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Test')CREATE USER [Test] FOR LOGIN [Test] WITH DEFAULT_SCHEMA=[dbo]USE [ReportServer1]GOexec sp_addrolemember N'db_datareader', N'Test'GOUSE [ReportServer1]GOGrant CONNECT ON Database::[ReportServer1] TO [Test] GOUSE [ReportServer1]GOGrant SHOWPLAN ON Database::[ReportServer1] TO [Test] GOUSE [ReportServer1]GOGrant VIEW DEFINITION ON Database::[ReportServer1] TO [Test] GOUSE [ReportServer1]GOGrant EXECUTE ON [dbo].[AddBatchRecord] TO [Test] GOUSE [ReportServer1]GOGrant ALTER ON [dbo].[AddModelPerspective] TO [Test] GOUSE [ReportServer1]GOGrant TAKE OWNERSHIP ON [dbo].[AddModelPerspective] TO [Test] WITH GRANT OPTION GO

RE: Permission scripting over all databases

Kishore.P — Tue, 18 Nov 2008 00:32:42 GMT

Very helpful script, used for many DBA's.

RE: Permission scripting over all databases

Oleg-461731 — Tue, 18 Nov 2008 00:22:28 GMT

Hi,thank you , it is great.Regards.

RE: Permission scripting over all databases

Telammica — Mon, 17 Nov 2008 03:15:46 GMT

Hi OlegThanks for the response. I did not take offline dbs into account. I have added that now and you can test it on your databases as soon as the change is approved. Code that I have added is" AND status&512 <> 512 ".An offline database has a bitfield with value 512. You can tweak this value to whatever your db situation is. These are the db states:1 = autoclose; set with sp_dboption.4 = select into/bulkcopy; set with sp_dboption.8 = trunc. log on chkpt; set with sp_dboption.16 = torn page detection, set with sp_dboption.32 = loading.64 = pre recovery.128 = recovering.256 = not recovered.512 = offline; set with sp_dboption.1024 = read only; set with sp_dboption.2048 = dbo use only; set with sp_dboption.4096 = single user; set with sp_dboption.32768 = emergency mode.4194304 = autoshrink.1073741824 = cleanly shutdownRegards

RE: Permission scripting over all databases

Oleg-461731 — Mon, 17 Nov 2008 01:33:00 GMT

Great script.But need to correct it.I have 20 databases on one server and 3 are offline for some special pruposes.This script gives me error and does not work if one of db's is offline.I think it must work and skip this kind of databases, because for example some of db's can be mirrored db and it is not possible to bring them online etc.Thank you.Regards,Oleg.

RE: Permission scripting over all databases

joseph.p.quaglia — Fri, 14 Nov 2008 14:40:15 GMT

Indeed, this is a very helpful script!! I appreciate it very much!!

Permission scripting over all databases

Telammica — Thu, 11 Sep 2008 09:59:05 GMT

Comments posted to this topic are about the item [B]Permission scripting over all databases[/B]