How to grant select permission to all the database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to grant select permission to all the database

Author

Topic

satishk
Starting Member

39 Posts

Posted - 2006-12-06 : 00:54:58

Hi,
can anyone advice me how to grant select permission to all the databases

Regards
Satish

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-12-06 : 20:59:08

go to each database, add the user to db_datareader role

--------------------
keeping it simple...

satishk
Starting Member

39 Posts

Posted - 2006-12-14 : 08:36:04

That is tedious.
Hence I want to have script which can create logins and assign role and grant or deny permission

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-14 : 09:51:32

It's pretty easy to do depending on how complex you want the script to be. I've got one that adds a read only login to all the databases (user) for reporting.

Check out BOL. sp_grantlogin, sp_addlogin, sp_grantdbaccess, sp_addrolemember, fixed database roles.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-14 : 10:17:14

Finally found it....

Declare @Login nvarchar(255) --NT or SQL login
, @Pwd nvarchar(255) --SQL login pwd, not needed for NT login
, @Default nvarchar(255) --Default DB assigned to login
, @DBRole nvarchar(255) --Fixed database role

Set @Login = 'Reader'
Set @Pwd = 'reader'
Set @Default = Null
Set @DBRole = 'db_datareader'

If @Default Is Null
Select Top 1 @Default = name
From master.dbo.sysdatabases
Where dbid > 4

If Exists (Select * From master.dbo.syslogins Where name = @Login)
Begin
Print 'Login already exists.'
Goto DBAccess
End

If (Select CharIndex('\', @Login, 0)) > 1
Exec master.dbo.sp_grantlogin @Login
Else
Exec master.dbo.sp_addlogin @loginame = @Login
, @passwd = @Pwd
, @defdb = @Default

DBAccess:

Create Table #Security (sql nvarchar(1000))

Insert Into #Security
Select 'If Not Exists (Select * From ' + name + '.dbo.sysusers Where name = ''' + @Login + ''')
Exec ' + name + '.dbo.sp_grantdbaccess ''' + @Login + ''', ''' + @Login + ''''
From master.dbo.sysdatabases
Where dbid > 4

Exec master.dbo.sp_ExecResultSet 'Select sql From #Security'

Truncate Table #Security

Insert Into #Security
Select 'If Not Exists (Select *
From ' + name + '.dbo.sysusers u1
Inner Join ' + name + '.dbo.sysmembers m On u1.uid = m.memberuid
Inner Join ' + name + '.dbo.sysusers u2 On m.groupuid = u2.uid
And u2.name = ''' + @DBRole + '''
Where u1.name = ''' + @Login + ''')
Exec ' + name + '.dbo.sp_addrolemember ''' + @DBRole + ''', ''' + @Login + ''''
From master.dbo.sysdatabases
Where dbid > 4

Exec master.dbo.sp_ExecResultSet 'Select sql From #Security'

Drop Table #security

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-12-14 : 15:00:55

Ugh! Needless use of GOTO.

Instead of


IF EXISTS(...)
BEGIN
  PRINT 'Login exists'
  GOTO DBACCESS
END

Use this:


IF NOT EXISTS(..)
BEGIN
  --CREATE LOGIN
END
ELSE
BEGIN
  PRINT 'Login exists'
END

-- Do DBACCESS stuff

Much cleaner without a GOTO. :-)

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources