sql-server-2008


How to pass User and LogIn as a parameter to a stored procedure?


I have a script that drops the user if it exists and then recreates it. I have this repeated like 30 times. Shown below:
IF EXISTS (SELECT 1 FROM sys.sysusers AS s WHERE s.name = N'johndoe')
BEGIN
PRINT 'Dropping user johndoe...'
DROP USER johndoe;
END
CREATE USER [johndoe] FOR LOGIN [uat\johndoe];
I was wondering if it would be possible to write a stored procedure that accepts user name and domain name and then drops and recreates the user?
CREATE PROCEDURE dbo.usp_DropAndRecreateUsers
(#username varchar(100),
#domainName varchar(100))
AS
DECLARE #loginName varchar(200) = '['+#domainName+'\'+#username+']';
IF EXISTS (SELECT 1 FROM sys.sysusers AS s WHERE s.name = #username)
BEGIN
DROP USER #username;
END
CREATE USER #username FOR LOGIN #loginName;
so that I could call like:
EXEC usp_DropAndRecreateUsers #username='johndoe', #domainName='uat'
However, after writing a stored procedure, I realized it wouldn't be as easy as I thought. Which probably is the reason this wasn't already done. But still, is this even possible to do in SQL Server (2008 and above)?
What I did was (which doesn't work):
CREATE PROCEDURE dbo.usp_DropAndRecreateUsers
(#username varchar(100),
#domainName varchar(100))
AS
DECLARE #loginName varchar(200) = '['+#domainName+'\'+#username+']';
IF EXISTS (SELECT 1 FROM sys.sysusers AS s WHERE s.name = #username)
BEGIN
DROP USER #username;
END
CREATE USER #username FOR LOGIN #loginName;

Rather than running from variable, create query and execute it


Related Links

Raw SQL with “IF NOT EXISTS” doesn't execute
Settting up a SSMS Linked Server to DB2
SQL Dynamic Temp Table
pyodbc - what username/password is passed here?
Sql cursor locking
Convert NVARCHAR to MONEY or FLOAT format
SQL QUERY LIKE & IN TOGETHER
Is the express version of SQL Server 2008 compatible with databases from full version?
Getting error message “unknown user name or bad password” while executing the procedure in SQL Server 2008
Database is not connected to my website build on ASP.Net
What is correct way to declare varbinary in SQL Server 2008?
SQL query advice .. writing a query with pattern matching
SQL Work Week in format yyyyww
linked server to excel permissions
Issue with installing hotfix SQL Server 2008 SP1
SQL Server Max Function

Categories

HOME
swift
winapi
fparsec
jbehave
computer-vision
mainframe
safari
hyperledger-fabric
desktop
x11
cortex-a
jacoco
arm-template
google-people
google-api-java-client
boolean-expression
styles
apply
jquery-form-validator
kitematic
facebook4j
spring-test
bootstrap-typeahead
jflex
katharsis
metis
kong
image-quality
protobuf-net
postgresql-9.2
fabric-digits
viewstate
code-behind
ntfs-mft
xbim
scalafx
amazon-clouddrive
rdw
lumen-5.3
aqgridview
proof
production-environment
ogre3d
photography
restivejs
laravel-query-builder
payu
ebtables
aescryptoserviceprovider
firepath
gitweb
windows-nt
acm
code-readability
webhdfs
mogrify
xjc
ffprobe
ibm-data-studio
callstack
uptodate
moai
odftoolkit
scala-2.11
cg
sid
method-overriding
neoload
voldemort
opensocial
resource-files
dllexport
html-form-post
mstsc
cgimageref
dynamics-ax-2009
qtgui
device-emulation
arbor.js
bulbs
continuous-testing
deploying
die
nemerle
executable-format
scalaxb
post-redirect-get
chatroom
querypath
visual-c++-2008-express
callgrind
dotproject
rd





Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm