January 6, 2009 at 5:25 pm
hi,
We had installed and configured biztalk server and the biztalk databases sit in sql server 2005. For, installation of biztalk , we require login ABC with sysadmin rights. After installation we need to revoke the sysadmin privilege and give securityadmin server role and db_securityadmin,db_accessadmin,db_ddladmin database role.
Here is the scenario:
I have a login ABC which has Sysadmin rights.
Now, I want revoke sysadmin rights for this login and give security admin server role and user mapping For database xyz, I need to give db_securityadmin,db_accessadmin,db_ddladmin.
So to do this I went Security->logins->ABC-Properties->uncheck sysadmin role and give securityAdmin->usermapping select the database xyz and give db_securityadmin,db_accessadmin,db_ddladmin then say ok Iam getting the following error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Add member failed for DatabaseRole 'db_accessadmin'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot use the special principal 'dbo'. (Microsoft SQL Server, Error: 15405)
In user mapping I have
database user defaultschema
xyz dbo dbo
here, The server role is changed to securityadmin from sysadmin but the database roles are not changing.
In next attempt I did the the following:
Security->logins->ABC-Properties->uncheck sysadmin role and give securityAdmin->usermapping Uncheck the database xyz and check it again and give db_securityadmin,db_accessadmin,db_ddladmin then say ok Iam getting the following error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Rename failed for User 'dbo'. (Microsoft.SqlServer.Smo)
Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)
In this case:
In user mapping I have
database user defaultschema
xyz def\abc
here, The server role is changed to securityadmin from sysadmin but the database roles are not changing.
plz advice How can I revoke this sysadmin role and give securityadmin server role and db_securityadmin,db_accessadmin,db_ddladmin database roles for ABC login.
Many thanks
January 6, 2009 at 8:29 pm
Its happening because the owner of the database is this login "xyz". To revoke permissions from it change the database owner to something else for eg. Exec sp_shangedbowner 'sa' and then go under security-->logins and it will now allow you to revoke the permissions.
HTH,
MJ
January 6, 2009 at 10:57 pm
Thanks Manu...its resolved my issue.
January 15, 2009 at 5:57 pm
But something is not working after revoking the privileges,if I want to revert back to the privileges that I have before what should I do?
January 19, 2009 at 6:39 am
Give back the previous access and role membership one by one and see which one allow it to work..
MJ
April 18, 2012 at 9:31 am
Thanks so very much for this insight... Save the day, the bacon and several sleepless nights that were yet to come.
FYI ! --> Please make a spelling change for the command is: Exec sp_Changedbowner 'sa'
My thoughts!
In MS Books on line for the “Alter User” command you will find this warning:
"The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role.
All members of the sysadmin fixed server role have a default schema of dbo."
Which is exactly what was happening to me, I unintentionally set the userid 'TestR' to sysadmin... (my bad)
This clobbered every default schema declaration in every database on this server instance.
-----------
--Here is my syntax where I had previously/ erroneously done the next line
--EXEC sys.sp_Addsrvrolemember @loginame = N'TestR', @rolename = N'sysadmin';
---
---Here is what I did to correct the issue.
Use Master
go
Exec sp_Changedbowner 'sa' --- This blogs solution followed by the next command
go
EXEC sys.sp_DROPsrvrolemember @loginame = N'TestR', @rolename = N'sysadmin';
---
Once done, the command, "select * from XMA.." , revealed the default schema of ‘APP’ in the XMA database, it was as if this server level Role was completely overriding/masking my lower user level actions.
Again Many Thanks, A BIG Lesson learned !!!
Hank Freeman
Senior SQL Server DBA/Data & Systems Architect
End.
🙂
Hank Freeman
Senior SQL Server DBA / Data & Solutions Architect
hfreeman@msn.com
678-414-0090 (Personal Cell)
April 18, 2012 at 9:31 am
Thanks so very much for this insight... Save the day, the bacon and several sleepless nights that were yet to come.
FYI ! --> Please make a spelling change for the command is: Exec sp_Changedbowner 'sa'
My thoughts!
In MS Books on line for the “Alter User” command you will find this warning:
"The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role.
All members of the sysadmin fixed server role have a default schema of dbo."
Which is exactly what was happening to me, I unintentionally set the userid 'TestR' to sysadmin... (my bad)
This clobbered every default schema declaration in every database on this server instance.
-----------
--Here is my syntax where I had previously/ erroneously done the next line
--EXEC sys.sp_Addsrvrolemember @loginame = N'TestR', @rolename = N'sysadmin';
---
---Here is what I did to correct the issue.
Use Master
go
Exec sp_Changedbowner 'sa' --- This blogs solution followed by the next command
go
EXEC sys.sp_DROPsrvrolemember @loginame = N'TestR', @rolename = N'sysadmin';
---
Once done, the command, "select * from XMA.." , revealed the default schema of ‘APP’ in the XMA database, it was as if this server level Role was completely overriding/masking my lower user level actions.
Again Many Thanks, A BIG Lesson learned !!!
Hank Freeman
Senior SQL Server DBA/Data & Systems Architect
End.
🙂
Hank Freeman
Senior SQL Server DBA / Data & Solutions Architect
hfreeman@msn.com
678-414-0090 (Personal Cell)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply