Database permission disapers

Question

Database permission disapers

stefan_hufnagel

SSC Enthusiast

Points: 110
More actions
October 25, 2013 at 3:52 am

#300458

Hi,
I have problem. I want to give some DB permission to a user. In chose the user from the logins and click the databasecheckbox and the rights i want to give.
After OK everythings seems to bee fine, but if I go back into properties of this user, the checkbox at Database is no longer set.
One more thing. The user that does not work is ext\doedel. There is another user doedel.
He has the right that I also want to give to ext\doedel.
If I want to remove the rights from user doedel I get an error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for User 'hb-ssis'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2550&EvtSrc=MSSQLServer&EvtID=15138&LinkId=20476
Any sugestions in which direction I could investigate
Thank you
stefan

Viewing 5 posts - 1 through 5 (of 5 total)

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

HanShi SSC-Dedicated Points: 33519 More actions · Answer 1

stefan_hufnagel (10/25/2013)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

The above quote indicates clearly why you can't remove the user. Look in the database at the available schemas and find the one (or more) that are owned by the user. Change the owner of these schemas or remove these schemas. After these actions you can remove the user.

The failure to add the permissions to a login could be caused if the user in the database is not correctly connected to the associated login. Execute the following SQL command and see if any users are returned in the result:

exec sp_change_users_login 'report'

You can match the users and the logins with the following command:

exec sp_change_users_login 'auto_fix', '{username}'

For more information about the "sp_change_users_login" command, see: http://technet.microsoft.com/en-us/library/ms174378.aspx

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

stefan_hufnagel SSC Enthusiast Points: 110 More actions · Answer 2

Hi HanShi,

Thank you for your relply!

Unfortunatly I already ran the statement you quoted.

'exec sp_change_users_login 'report' '

The resultset was empty.

cu

Stefan

HanShi SSC-Dedicated Points: 33519 More actions · Answer 3

You mentioned you can't create the database user and give permissions from the LOGIN (ssms: instance - security - logins - properties existing login). But are you able to create the USER from the database and associate this user to the LOGIN (ssms: instance - database - security - users - new user)?

To which LOGIN is the current USER "doedel" connected? Is this the same LOGIN you wanted the new USER "ext\doedel" to be connected to?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

stefan_hufnagel SSC Enthusiast Points: 110 More actions · Answer 4

Hi,

with some SQLcode everything is fine again.

/*

use DPR_

go

create user [DEF] for Login [DEF]

go

use DPR_

EXEC sp_addrolemember N'db_datareader', N'DEF'

go

use DPR_

EXEC sp_addrolemember N'db_datawriter', N'DEF'

go

*/

I have no idea why gui would not make it run.

Thank you for helping and a nice weekend

best greetings

Stefan