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

Call MS SQL Server Stored Procedure with multiple parameters inclusive of table-value parameter
Converting null value to default value in database
Subquery does not return results
write a TRANSFORM statement in Sql Server
i'm query max value it's duplicate values​​
How to add time in sql server 2008?
Copy a DB table from a remote server to local system in sqlserver
Syntax Error with Full-Text Search
Better way of identifying ranges of numbers in SQL Server other than a cursor
Empty Text File in SSIS Package
Query syntax is not valid near identifier 'PIVOT'
stock management
Insert into several tables at once with Entity Framework
Impact on application in migrating from sql server 2005 to 2008
Amazon AWS RDS Create database permission denied
Special case Counting of Records

Categories

HOME
udp
signalr
tinyos
aem
avro
cocos2d-x-3.0
parse-server
angular2-directives
zend-framework2
phonegap-cli
file-io
constructor
vuex
ssr
wordpress-theming
data-synchronization
lenskit
r-lavaan
tree-traversal
jogl
pycrypto
statusbar
iup
rworldmap
http-method
branch
metadata-extractor
classpath
nesc
hana-studio
mangodb
forum
pep8-assembly
polymorphism
fabric-digits
windows-10-iot-core
rider
ksoap
redhat-datavirt
declare
socketscan
viewgroup
xbim
vcf
chown
ti-basic
applepayjs
ticker
scd
instruction-set
mercury
iron.io
vici
sql-server-administration
android-navigationview
ebtables
mix
ruby-2.0
radians
portfolio
insertion-sort
datainputstream
hornetq
anti-patterns
risk-analysis
twitter-rest-api
rspec3
gulp-livereload
sysfs
google-earth-plugin
moai
viewflipper
io.js
proxies
lumx
resource-files
flash-cc
convex-polygon
goinstant
cgimageref
pascals-triangle
qsqltablemodel
feof
macruby
gjs
querystringparameter
electronic-signature
windows-live-id
sitemappath
external-assemblies
grooveshark
front-controller
graph-layout
evb
configurable
weborb
ajaxpro
cots

Resources

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
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App