Usuarios, permisos y SQL Server

Recientemente me encontré ante la situación de tener que configurar, en un proyecto de Database de Visual Studio 2008, una serie de usuarios, permisos y roles.

En concreto, necesitaba crear un rol que pudiera ejecutar procedimientos almacenados y hacer SELECTs sobre un determinado esquema. A este rol, a su vez, pertenecería un usuario que también debía crearse dinámicamente en el script. Tras una mañana de investigación, estas son mis conclusiones.

Logins y Users

En SQL Server hay que distinguir entre Logins y Users. Los Logins se crean a nivel de servidor de base de datos. Pueden crearse como Logins de SQL Server, o pueden crearse a partir de una cuenta Windows, lo que en general es más seguro por utilizarse la autenticación integrada. En mi caso era precisamente eso lo que pretendía hacer, crearlos a partir de una determinada cuenta Windows. Esto es muy sencillo de hacer, basta un comando como el que se puede ver a continuación:

USE [yourdb]
GO
CREATE LOGIN [YourDomainYourUser] FROM WINDOWS
GO

Sin embargo, esto tiene una pega: no se puede hacer en los proyectos de base de datos de Visual Studio 2008, o al menos yo no he encontrado el modo. Tiene su sentido, de todas formas, puesto que estos proyectos no dejan de ser la creación de una base de datos concreta, y no debería afectar a la configuración del servidor en sí.

Una vez creado el Login, ya podemos crear un usuario y asociarlo a él. Esta parte sí se puede añadir al proyecto de BD de VS2008, en concreto en la carpeta Users dentro de Security. El TSQL necesario sería algo así:

CREATE USER [YourDomainYourDbUser] FROM LOGIN [YourDomainYourUser]
GO

Bien, con esto ya tendríamos nuestro usuario concreto de la base de datos creado, asociado a un Login a nivel del servidor.

Roles y permisos

El siguiente paso es crear el rol al que vamos a asociar el usuario que recién hemos creado. Este rol va a pertenecerle a dbo. No podemos darle sus permisos directamente en el script que se crea en la carpeta Database Roles de Security, esa parte tendremos que añadirla en un script de Post-deployment. Así que en el script de creación del rol simplemente tendríamos la siguiente sentencia TSQL:

CREATE ROLE [YourNewRole] AUTHORIZATION [dbo]
GO

Quedarían dos pasos por ejecutar: darle los permisos al rol que necesite y asociarle el usuario que creamos al principio.

La primera parte se completa con una sentencia parecida a ésta. Sería necesario indicar qué esquema de los existentes en la base de datos, va a poder el rol ejecutar sus SPs.

GRANT EXECUTE ON SCHEMA ::[YourSchema] TO [YourNewRole]
GO

En cuanto a la segunda, bastaría algo como lo que sigue, apoyándonos en uno de los procedimientos almacenados del sistema:

EXEC sp_addrolemember N'YourNewRole', N'YourDomainYourUser'
GO

Bibliografía: