x

Database Permissions

I am trying to assign a login for a SQL instance to have enough permissions to add themselves as a user to a specific database without giving them full SA permissions to the instance. Is there a permission that will allow me to do this? The user has create any database permissions and creates the database in question. The user then performs an action and is removed but needs to be re-added back but does not have the permission.

Currently I am creating the login with these permissions:

CREATE LOGIN [testuser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO

DENY VIEW ANY DATABASE to [testuser]

GO

GRANT CREATE ANY DATABASE TO [testuser]

GO

more ▼

asked Sep 11, 2014 at 01:00 PM in Default

avatar image

sqlLearner 1
972 43 51 57

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

The security admin can create logins as you specify. The drawback is the role can create logins and should be treated as being equivalent to sa.

more ▼

answered Sep 11, 2014 at 01:09 PM

avatar image

CirqueDeSQLeil
5.5k 11 13 20

(comments are locked)
10|1200 characters needed characters left
  1. Put the logic in stored procedure and sign the procedure with a certificate

  2. grant them a premission to execute procedure and this should do.

more over here http://sommarskog.se/grantperm.html

more ▼

answered Sep 16, 2014 at 03:45 PM

avatar image

emil87b
411 1 5 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x448
x447
x217
x123
x109

asked: Sep 11, 2014 at 01:00 PM

Seen: 469 times

Last Updated: Sep 16, 2014 at 03:45 PM

Copyright 2018 Redgate Software. Privacy Policy