question

ETHMAN5 avatar image
ETHMAN5 asked

How to create 'XXX' account on Sql Server 2000 with Read only privileges on all databases.

Hi I never worked on SQL server 2000 can some one tell me How can i create 'XXX' account on Sql Server 2000 with Read only privileges on all databases. Thanks
sql-server-2000securityaccess
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave_Green avatar image
Dave_Green answered
I've interpreted your question to mean that you want the 'XXX' account to have the ability to read any data in a user database on the server. Add the user using Enterprise Manager or sp_addlogin, then add them to the db_datareader role on the databases you want to add them to (you will need to make them a user in each of those databases). You could use sp_MSforeachdb to do this for all databases, depending on the number of databases on the server. Obviously you will want to review the implications of the role in Books Online; you may require additional access to execute stored procedures. You should also consider whether the account will be SQL or Windows Authentication.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

In addition to @Dave_Green's point about whether the account will be SQL or Windows, also think - are you going to want more than one user set up like this? If so, consider making them all a member of a particular AD group, and applying these permissions to that group, rather than to an individual user. The code is the same.
2 Likes 2 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
In addition to @Dave_Green 's answer: If you want the user to have ReadOnly access, you should also add the user to the db_denydatawriter role. To do that for all user databases in an instance, using exec sp_addlogin @loginame='testuser', @passwd='Pa$$w0rd' exec sp_MSForEachDB @command1=' IF ''[?]'' not in (''master'',''model'',''msdb'',''tempdb'') BEGIN exec [?].dbo.sp_adduser @loginame=''testuser'' exec [?].dbo.sp_addrolemember ''db_datareader'',''testuser'' exec [?].dbo.sp_addrolemember ''db_denydatawriter'',''testuser'' END ' But please bear in mind that sp_MSForEachDB is undocumented and is used at your own risk.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ought to get an extra +1 for excluding system databases too
2 Likes 2 ·
Would like to give you an extra +1 for mentioning that sp_MSforeachDB is undocumented...
0 Likes 0 ·
Oh, I'm blushing here.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.