Builtin Administrators

Question

Post reply

Builtin Administrators

Henry-309942

SSC Eights!

Points: 822
More actions
May 29, 2008 at 8:55 am

#123996

I am trying to restrict access to my sql server by removing the builtin admin and create an account for read only access. I have an account for the sql group that has admin rights and all the application has an account to access the databases. But the account for read only is erroring out on my workstation when I test the script. Please see the script below and the error message.
--My script
--[VHO\SQLRO] -- create SQLRO group in all the databases and grant readonly access permission
declare @sql varchar(max)
declare @dbName sysname
DECLARE @account SYSNAME
DECLARE @VHO_Name nvarchar(6)
select @VHO_Name = LEFT(@@servername, 6)
SELECT @account = @VHO_Name + N'\SQLRO'
DECLARE db_cursor CURSOR FOR
select name from sys.databases where database_id > 4 and name <> 'Distribution'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbName
WHILE @@FETCH_STATUS = 0
begin
set @sql = 'use '+@dbname + ';'
select @sql
--exec @sql
set @sql = 'CREATE USER [' + @account + '] FOR LOGIN ['+@VHO_Name+'\SQLRO]; '
--exec @sql
select @sql
set @sql = 'EXEC sp_addrolemember db_datareader ,[' +@VHO_Name+'\SQLRO];'
select @sql
exec @sql
FETCH NEXT FROM db_cursor INTO @dbName
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO
--Error Message I am getting when I execute teh above script on my workstation
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 203, Level 16, State 2, Line 27
The name 'EXEC sp_addrolemember db_datareader ,[TXCDTL\SQLRO];' is not a valid identifier.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

MANU-J.

SSC-Dedicated

Points: 31126

May 29, 2008 at 9:18 am

#821814

Whats your workstation name?

Manu

Henry-309942 SSC Eights! Points: 822 More actions · Answer 2

My workstation name is TXCDTLT01HO2451 and I am getting the first 6 char TXCDTL

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

Why don't you specify the complete server name. half name is not allowing SQL Server to recognize the mentioned group.

Manu

Kyle Neier , SSCrazy Points: 2252 More actions · Answer 4

First -- I don't believe that you can have the "\" in a user name of a database. Try removing that and see if it works for you.

Second -- the database context that you are switching to isn't working. Using the "EXEC" statement keeps everything within that context. So, executing "USE dbname" is switching it within that context. The next exec statement is within it's own context. You should concatenate the sql and run it all at once for it to work the way I presume you are intending it to work.

Kyle

Manoj-485464 SSCrazy Points: 2717 More actions · Answer 5

Probably it has been already posted in other thread. Anyway. Before deleting BuilinAdmin you should be having SQL_SVC a/c and that a/c should have permission as a SYsadmin. but make sure from AD group that svc a/c should be having login permission.

Manoj

MCP, MCTS (GDBA/EDA)

Henry-309942 SSC Eights! Points: 822 More actions · Answer 6

all that is taking care of if you read the posting. i just need help with the script i posted. thanks.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 7

Please change

from

select @VHO_Name = LEFT(@@servername, 6)

to

select @VHO_Name = LEFT(@@servername, 15)

Manu

Henry-309942 SSC Eights! Points: 822 More actions · Answer 8

manu

This did not make any change it will only give you the whole name of the machine which is not my problem. The problem is at the

exec @sql