May 24, 2007 at 8:46 am
Hi all,
I'm new to MS SQL Server 2005 Administration and have a question.
In my network setup, a C++ application runs on a remote machine and periodically creates a new database on a SQL server 2005 (running on Windows Server 2003 of a separate machine)
I have to create an account, dba_test with readonly access to all existing databases on the server. When a new database is created, dba_test should be able to open, view, and read its tables (but not to modify any data at all).
How can I do this ?
Thanks.
May 24, 2007 at 9:21 am
On sql server management studio open up the security folder right click on a login,
select new login
create user as needed in appropriate box
if NT authentication is needed enter the domain\username (no need for a password) if sql server user supply appropriate password.
In User Mapping check needed databases
and on the panel below check db_datareader
This should grant the user read access to all the checked databases
Hope that is what you needed
Mike
May 24, 2007 at 10:30 am
Thanks Mike for quick reponse.
I forgot to mention that I need to write a stored procedure (or a script) to automate this task. The server has quite a few of existing databases. It's too tedious to grant a user account the access to every new database that is created by the application.
May 24, 2007 at 3:13 pm
An alternative is to grant the permissions in the model database - each new database will inherit permissions going forward. As far as existing stored procedures, views, etc you can use something similar -
-- PART I
EXEC sp_addrole 'DB_ROLENAME', 'dbo'
go
EXEC sp_addrolemember 'IS Developers','DOMAINNAME\GROUPNAME' go
--PART II
DECLARE @ProcName sysname ,
@usercredentials sysname
set @procname=''
set @usercredentials='[DB_ROLENAME]'
WHILE @procname is not null
begin
SELECT @procname = min(name)
FROM sysobjects
WHERE xtype='P' and name > @procname and name not like 'dt_%'
and name not like 'sp_MS%'
IF @procname is not null
BEGIN
EXEC('GRANT VIEW DEFINITION ON ' + @procname+ ' TO ' + @usercredentials)
--PRINT 'Permissions granted on '+ @procname+ ' TO ' + @usercredentials
END
END
Tommy
Follow @sqlscribeViewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply