Hot answers tagged

7

You'll need to determine how the logins have access to run DDL in master. The following query will return a list of logins with server-level role membership: SELECT ServerName = @@SERVERNAME , RoleName = roles.name , MemberName = members.name , IsEnabled = CASE WHEN members.is_disabled = 1 THEN 0 ELSE 1 END FROM sys.server_role_members srm ...


7

This can be accomplished using a Certificate and module signing (i.e. ADD SIGNATURE). Using Impersonation via EXECUTE AS can get messy, and it leaves the possibility of someone else Impersonating the "allowed" User, or changing the contents of a module that is using the EXECUTE AS. But with module signing: the Certificate-based User cannot be Impersonated ...


6

Maybe the wording is unclear, but least privilege applies to all activities and should never be ignored. The least privilege you need to perform database maintenance is sysadmin. Lower privileged users could perform those activities too (I'm thinking of db_owners), but the least privilege needed to perform any activity on a SQL Server instance is sysadmin, ...


6

There is no inbuilt method for granting permissions on tables matching a pattern. You need to grant it to the individual tables. It would be quite easy to generate the required script with a query against sys.tables for names like 'VVC[_]%' though. Instead of using a VVC prefix you could create a VVC schema. Then you can grant select permissions on the ...


6

Assuming they're all in the dbo schema: DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'GRANT SELECT ON dbo.' + QUOTENAME(t.name) + N' TO [User1];' FROM sys.tables AS t WHERE t.name LIKE N'VVC[_]%' AND t.[schema_id] = 1; EXEC sys.sp_executesql @sql; But Martin is right, it would be much better to use a schema than a string prefix.


5

If you're happy using EXECUTE AS (trusting users that can impersonate), an alternative is: Tables CREATE TABLE dbo.Test ( TestID integer IDENTITY PRIMARY KEY, SomeDate datetime NOT NULL ); GO CREATE TABLE dbo.TestArchive ( TestID integer PRIMARY KEY, SomeDate datetime NOT NULL ); Users -- Ordinary user with the ability to insert to the ...


4

You can grant admin access using this code: CREATE LOGIN [DOMAIN\ADGroupName] FROM WINDOWS; GRANT CONTROL SERVER TO [DOMAIN\ADGroupName]; That will allow any user who is a member of [DOMAIN\ADGroupName] to login to SQL Server without specifying a username/password, and will grant those users admin access to the entire instance, including all databases. ...


3

Some of what you are asking for is actually pretty simple. Permissions for your AD Group You create a login for your AD group CREATE LOGIN [domain\AD Group] FROM WINDOWS Then grant it the access you want. You say you want to grant it Admin access but unless this is your DBA team I wouldn't add the AD Group to something like sysadmin. Instead grant ...


3

Triggers run, by default, under the security context of the principal who caused the trigger to fire. In order to change this behavior, you'll need to create the trigger using the WITH EXECUTE AS OWNER clause. Below is an example which shows how that works. WITH EXECUTE AS OWNER allows the trigger to run in the security context of the database owner, ...


3

A SQL Server login is used to authenticate the principal at the server level, and to provide server level permissions. A database user maps to zero or one login and is used to provide a security context within the database. Database permissions are assigned to database principals, including roles, not directly to logins. User names are generally not ...


2

You need the USAGE privilege (at least) for the schema as well: GRANT USAGE ON SCHEMA something TO GROUP data_viewers; Related Postgres example: Permission for sequence in another schema Remember you only granted permissions to already existing tables. Does not apply to tables created later. To cover those, too: ALTER DEFAULT PRIVILEGES FOR USER ...


2

Interesting one - difficult to pin this one down. Have you thought about looking at the public role? sp_helprotect 'CREATE PROCEDURE',NULL,NULL,'s' Does that bring you back anything?


2

Yes. What you have will do what you want. I do question the need for UserA to have db_owner access even on database A though. Do they have a need to be able to drop the database? Run backups that could interrupt your backup chain? Change recovery model (same problem)? Generally even if a developer has complete control over a given database I would ...


2

I am not quite sure that I understand your question clearly, so please bear with me on the issue of Logins and Users. It appears to be, in your case, SQL Server logins and not Active Directory accounts, but they behave essentially the same within a server and database. Also, for what it is worth, it seems that some step or steps are missing from your ...


2

sp_help_revlogin See: How to transfer logins and passwords between instances of SQL Server on MSDN Worked reasonably well for me this past summer.


2

I have a couple of stored procedures that I wrote and use on a regular basis. sp_SrvPermissions sp_DBPermissions They each have three outputs. Principals : A list of the database/server principals and associated info including create/drop scripts. Role membership : A list of what roles each database/server principal belongs to along with add/drop ...


2

The two most common scenarios for security via a linked-server are: Create a SQL login on the target SQL Server, and use that login in the definition of the linked server. You then GRANT and DENY access to that login on the target server objects as necessary, using the principle of least privilege. Define the SQL Server to allow "pass through" ...


2

A post for handling job permissions can be found at: Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it This post outlines: Using standard SQL Agent roles such as SQLAgentOperatorRole. Note: Active Directory Groups are not able to login to start a job. Erland Sommarskog's post ...


1

The problem is that AutoClose option for database is set to True by default when you create a database with Express Editions. Go to DB properties, switch it to false and try again dropping it or use below script before dropping ALTER DATABASE [MyDb] SET AUTO_CLOSE OFF GO


1

I wasn't paying attention to, or correctly interpreting, the results of the query from Kin's answer to this question: List all permissions for a given role? In the results of the query, you'll see execute listed but with no specific object type or object name!


1

GRANT EXECUTE TO [principal] is simply a shortcut for GRANT EXECUTE ON DATABASE::<dbname> TO [principal]; You can check this using the following: SELECT dp.name , perms.class_desc , perms.permission_name , perms.state_desc FROM sys.database_permissions perms INNER JOIN sys.database_principals dp ON perms.grantee_principal_id = ...


1

What you are looking for does not exist in MySQL 5.5 Unfortunately, the ALTER USER command for MySQL 5.6 is limited. All you can do is ALTER USER user@host PASSWORD EXPIRE; In MySQL 5.7, you could run the ALTER USER command as follows ALTER USER user@host REQUIRE NONE; When it comes to MySQL 5.5, you did the most expedient way possible. Great !!! A ...


1

janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege. You got at least one aspect of the command backwards in all your attempts: Alter default privileges for the role that creates objects - which in turn grants privileges to another role. I.e.: ALTER DEFAULT PRIVILEGES FOR ROLE janerole IN SCHEMA public GRANT ...


1

Something like that could be achieved through dynamic sql, or via using stored procedures instead of views (for selecting) or triggers (for controlling inserts/updates).alter But you can also create the memberview to return values according to the currently logged on user, which is probably more practical: create view memberview as select Dept, Name ...


1

Unlike sysadmin which bypasses checks, the built in database roles aren't so special that they can't be overridden with a DENY. Try looking at Exec sp_helpprotect Null, 'Username' and seeing what DENY records show up.


1

By default the DEFINER is the only user that can read stored programs. If DEFINER is left out then the default user that is created the function is used. This is a security feature. Definer and invoker security contexts differ as follows: A stored program or view that executes in definer security context executes with the privileges of the account named ...



Only top voted, non community-wiki answers of a minimum length are eligible