SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 How to grant select permission to all the database		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

satishk
Starting Member

39 Posts
Posted - 12/06/2006 :  00:54:58  Show Profile  Reply with Quote
Hi,
can anyone advice me how to grant select permission to all the databases

Regards
Satish

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts
Posted - 12/06/2006 :  20:59:08  Show Profile  Send jen a Yahoo! Message  Reply with Quote
go to each database, add the user to db_datareader role

--------------------
keeping it simple...
Go to Top of Page

satishk
Starting Member

39 Posts
Posted - 12/14/2006 :  08:36:04  Show Profile  Reply with Quote
That is tedious.
Hence I want to have script which can create logins and assign role and grant or deny permission
Go to Top of Page

JoeNak
Constraint Violating Yak Guru

USA
292 Posts
Posted - 12/14/2006 :  09:51:32  Show Profile  Reply with Quote
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.
Go to Top of Page

JoeNak
Constraint Violating Yak Guru

USA
292 Posts
Posted - 12/14/2006 :  10:17:14  Show Profile  Reply with Quote
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
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts
Posted - 12/14/2006 :  15:00:55  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000